Index/Match across multiple worksheets

[xiregulatorix]

Hi all,

I have multiple sheets, and I would like to do a lookup or index/match by searching through column A in each sheet.

Example:

In all 10 sheets:

Cells A1:A10 all have ID numbers in them
Cells B1:B10 all have Names of people in them
The same type of information is in the same cells across all 10 sheets (i.e. Sheet1, sheet2, sheet3, etc.).

What I'd like to do is create a new sheet, and write a formula so that if I type in an ID # in cell C1, it will search through all of the other 10 sheets for the ID# and spit out the corresponding name

In my new sheet, I have something like:

=index(sheet1:sheet10!$B$1:$B$10,MATCH(C1,sheet1:sheet2!$A$1:$A$10,0))

where C1 in this case would be an ID # that you can type in. When I try this, I just get #VALUE!

Any ideas?

Thanks!

 

[Robert Mika]

Hi all,

I have multiple sheets, and I would like to do a lookup or index/match by searching through column A in each sheet.

Example:

In all 10 sheets:

Cells A1:A10 all have ID numbers in them
Cells B1:B10 all have Names of people in them
The same type of information is in the same cells across all 10 sheets (i.e. Sheet1, sheet2, sheet3, etc.).

What I'd like to do is create a new sheet, and write a formula so that if I type in an ID # in cell C1, it will search through all of the other 10 sheets for the ID# and spit out the corresponding name

In my new sheet, I have something like:

=index(sheet1:sheet10!$B$1:$B$10,MATCH(C1,sheet1:sheet2!$A$1:$A$10,0))

where C1 in this case would be an ID # that you can type in. When I try this, I just get #VALUE!

Any ideas?

Thanks!

Hello and welcome to MrExcel.
Look here
http://www.mrexcel.com/forum/excel-questions/650842-vlookup-across-multiple-tabs.html#post3227983
Change Return to Sheet:

=VLOOKUP(C1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:10"))&"!A2:A100"),B1)>0,0)&"!A2:B100"),2,0)

 

[Aladin Akyurek]

Hi all,

I have multiple sheets, and I would like to do a lookup or index/match by searching through column A in each sheet.

Example:

In all 10 sheets:

Cells A1:A10 all have ID numbers in them
Cells B1:B10 all have Names of people in them
The same type of information is in the same cells across all 10 sheets (i.e. Sheet1, sheet2, sheet3, etc.).

What I'd like to do is create a new sheet, and write a formula so that if I type in an ID # in cell C1, it will search through all of the other 10 sheets for the ID# and spit out the corresponding name

In my new sheet, I have something like:

=index(sheet1:sheet10!$B$1:$B$10,MATCH(C1,sheet1:sheet2!$A$1:$A$10,0))

where C1 in this case would be an ID # that you can type in. When I try this, I just get #VALUE!

Any ideas?

Thanks!

Somewhat different although a variation vis-a-vis the suggestion Robert forwards...

1. Create a range with the relevant sheet names, Select this range and name the selection as SheetList.

2. Control+shift+enter, not just enter:

=VLOOKUP(C1,INDIRECT("'"&LOOKUP(REPT("z",255),
  IF(COUNTIF(INDIRECT("'"&SheetList&"'!A2:A10"),C1),
    SheetList))&"'!A2:B10"),2,0)

Replace the REPT("z",255) bit with 9.99999999999999E+307 if the sheet names are just numbers.