Advanced search

Excel query across sheets

(43 Posts)
EBearhug Fri 31-Jan-14 20:31:32

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 Fri 31-Jan-14 20:32:37

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

FastWindow Fri 31-Jan-14 20:36:24

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 Fri 31-Jan-14 20:38:28

Vlookup on sheet 3????

Or an "if" formula?

flummoxedlummox Fri 31-Jan-14 20:40:17

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

Wolfcub Fri 31-Jan-14 20:41:05

It sounds like you need an If formula

ItsATIARA Fri 31-Jan-14 20:43:16

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

StealthPolarBear Fri 31-Jan-14 20:47:37

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

StealthPolarBear Fri 31-Jan-14 20:48:07

No cos its the value anywhere in col b

StealthPolarBear Fri 31-Jan-14 20:48:30

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

RandomMess Fri 31-Jan-14 20:49:39

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

The "help" function is very good though?

ItsATIARA Fri 31-Jan-14 20:50:15

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

StealthPolarBear Fri 31-Jan-14 20:51:55

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

StealthPolarBear Fri 31-Jan-14 20:52:16

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

ItsATIARA Fri 31-Jan-14 21:02:34

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 Fri 31-Jan-14 21:04:23

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

EBearhug Fri 31-Jan-14 21:09:30

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 Fri 31-Jan-14 21:11:50

Oh uou big meanie smile
Started a thread for anyone who has pent-up

StealthPolarBear Fri 31-Jan-14 21:12:21


EBearhug Mon 03-Feb-14 16:34:13

In s1!a2, I am doing:


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 Mon 03-Feb-14 16:44:05

As a test replace i2 with the actual value in i2

StealthPolarBear Mon 03-Feb-14 16:44:32

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

StealthPolarBear Mon 03-Feb-14 16:45:40

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

EBearhug Mon 03-Feb-14 16:47:48

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

StealthPolarBear Mon 03-Feb-14 16:51:43

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)

Join the discussion

Join the discussion

Registering is free, easy, and means you can join in the discussion, get discounts, win prizes and lots more.

Register now