Excel query across sheets

[EBearhug]

What I want to do is have:

Sheet1!A2 take the value from Sheet2!ColumnE, where Sheet2!ColumnB = Sheet1!F2.

I think it will probably be some sort of lookup, or maybe a pivot table or something, but I can't get anything working so far. It's about 15 years since I last used my advanced Excel skills, so not only is everything very rusty, things have changed a bit between Excel 5 and Excel 2010...

I have tried the actual value, and variations within quote marks. It's going to be something annoyingly simple in the end. These things slways are...

(Have also checked for typos, seeing slways...)

I use false insteaf og true btw for an exact match. But you say you've tri ed that

It works for hlookup, except that's not a value which is useful.

Did you type the "s2!a2:b100" bit within the formula, or did you select it wth the mouse? If the former, then try the latter in case you've missed a '

Do you get any results at all?

Things I can think of being an issue:

1. If you copy down the formula, the row references to sheet 2 will change if you don't have $ signs in the range you are looking up.

1. The data looks like it has the same references, but has rouge spaces or formatting that means it isn't the same. A trim function can help with this.

Sorry rogue - not rouge.

I think index match should work, as below (assuming your data is on rows 2 to 30). Don't have excel to hand, but you should be able to get something similar to work.

=index (sheet2!E2:E30, match (sheet 1!f2, sheet 2!B2:B30,0))

But there id no need! Vlookup shoukd work easily tho indrx match sometimes better performance

Index match is my default I have to admit, VLOOKUP is banned in my workplace (for reasons I don't understand because I've never used it, probably something to do with the way we name ranges).

index match is better for performance and can also work looking right to left.
But I find it so much less intuitive!
No idea why vlookup would be banned - maybe it does;t work well with named ranges. I never use them

I will definitely check for rogue spaces, that's a possibility. I will also try index match.

(But vlookup should work, according to the syntax and everything.)

Or numbers stored as text - I think that can be a problem.

Yes, I tried that one, changing the formats. (It's text rather than numbers.)

Plesse send the sheet through! Curiosity is killing me!

Well, there has been a breakthrough, and on sheet2, the data I pick up to copy into the cell in s1, that needs to be on a column to the right of the one in which I'm looking up the initial value. And on my actual data, I was doing it the other way round, and it got all confused.

So I said I wanted:
Sheet1!A2 take the value from Sheet2!ColumnE, where Sheet2!ColumnB = Sheet1!F2.

If I moved Sheet2!F to before Sheet2!E, it all starts working - it can only read from left to right it seems.

So that's about 5 hours time spent on about 30 seconds actual work... But I have learnt something, so that's good.

That's really obvious now you say it. Index & match works even if you are reading right to left.