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...

it used to be Hlookup or Vlookup? not sure what it is now.

Vlookups only work if you have a table to refer to. A simple = should work and then if the second cell changes, the first should too? Hard to say out of context... Hth...

Vlookup on sheet 3????

Or an "if" formula?

If I understand correctly then yes =IF should work across sheets.

It sounds like you need an If formula

yes
=IF(Sheet2!ColumnB = Sheet1!F2,Sheet2!ColumnE,whatever you want to happen if that's not the case)

Vlookup (s1!f2, s2!b:e, 4, false)

No cos its the value anywhere in col b

I mean thats why an if wont work. Needs a vlookup.

it's Friday night, I've been drinking, I can't work it out...

The "help" function is very good though?

Oh yes of course. You need an IFERROR(vlookup(etc......

Btw s1 is sheet1 and s2 is sheet2
I do this stuff all day and am sure it will work

Btw s1 is sheet1 and s2 is sheet2
I do this stuff all day and am sure it will work

I also do this stuff all day but some ghastly mind parasite has apparently sucked my brains - ignore me and just type what stealth wrote.

Ouch at mind parasite - I think thats also known as thw help function

Thank you all. And you're right, it is Friday evening, and therefore, I'm not actually going to see if this works this evening. I was nearly there, I think.

Oh uou big meanie :)
Started a thread for anyone who has pent-up
geekiness

here

In s1!a2, I am doing:

=vlookup(i2,s2!a2:b100,2,true)

Because I want my s1!a2 to lookup s1!i2 in s2!a and to return what is in s2!b on the same row. Only it's not doing that, even when I try false or blank instead of true, or 1 instead of 2, or even b2:a100.

I'm currently getting #VALUE! back, which it says it will do if the index number is less than 1, but 2 or 1 is not less than one.

It is most frustrating. What am I doing wrong?

As a test replace i2 with the actual value in i2

Your descr I ption of what you want to do tallies with the formula

If yiu want to send it to me pm me I can have a look b later tonight

Basically, I want s1!a2=(select s2!b where s2!a==s1!i2). Except it's Excel, not a proper darabase.

Can you post the exact formula insteas od turning it into s1 etc (I know I started that and its a long shot but uou never know)

Are you sure the looked up value isnt #value?
What happens if you replace i2 with a value yiu definitely know is listed?