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

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(IN DIRECT("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}"

--
Jacob


"jude" wrote:

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

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

"Jacob Skaria" wrote:

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(IN DIRECT("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}"

--
Jacob


"jude" wrote:

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

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

"jude" wrote:

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

"Jacob Skaria" wrote:

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(IN DIRECT("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}"

--
Jacob


"jude" wrote:

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