vlookup

[jude]

Hi
I have a spreadsheet with multiple sheets in it. I need to do a vlookup on
a value (that is input as part of a list) across several sheets. Is this
possible?
The sheets all look the same, I am looking for a shortcut rather than cut &
pasting the data.
cheers
Jude

 

[Jacob Skaria]

Try the below array formula which will lookup the value in current sheet cell
C1 in Sheet1,Sheet2,Sheet3 ColA and return the matching value from ColB of
these sheets

=VLOOKUP(C1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:3"))&"!A:A"),B1)>0,0)&"!A:B"),2,0)

You create array formulas in the same way that you create other formulas,
except you press CTRL+SHIFT+ENTER to enter the formula. If successful in
'Formula Bar' you can notice the curly braces at both ends like "{=<formula>}"

 

[jude]

Thanks, but I think I need a bit more info - plus I prob didn't provide
enough info.

My look up id's are in column A from A7 downwards. I haven't sorted it in
ascending order as I was using the False in the range lookup.
The info I need to return is in column I from I7 downwards.
Each of the sheets are named something different. The info in each of the
sheets in column I is from a validated drop down list.

I can get a vlookup to work from one sheet, but I think that I am out of my
depth to understand the formula below and how to modify it to do what I need
it to do.
Thanks
Jude

 

[jude]

Hi Jacob,

I have run a test based on the below formula and it doesn't work. The test
was to return a name, but it is coming up with a value of 0. Hence I am not
sure what I am doing wrong. Do you have any further suggestions?

Many thanks
Jude