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

[Parietal]

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

[FastWindow]

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

[RandomMess]

Vlookup on sheet 3????

Or an "if" formula?

[flummoxedlummox]

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

[Wolfcub]

It sounds like you need an If formula

[ItsATIARA]

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

[StealthPolarBear]

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

[StealthPolarBear]

No cos its the value anywhere in col b

[StealthPolarBear]

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

[RandomMess]

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

The "help" function is very good though?

[ItsATIARA]

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

[StealthPolarBear]

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

[StealthPolarBear]

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

[ItsATIARA]

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.

[StealthPolarBear]

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

[EBearhug]

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.

[StealthPolarBear]

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

[StealthPolarBear]

here

[EBearhug]

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?

[StealthPolarBear]

As a test replace i2 with the actual value in i2

[StealthPolarBear]

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

[StealthPolarBear]

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

[EBearhug]

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

[StealthPolarBear]

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)

[StealthPolarBear]

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