Index and Match Multiple Worksheets with Different Columns

[Pc1x1]

I have searched the forum, and noticed this question was sort of asked. However I believe my problem is a little different, as I am running into some errors.

I need to index and match two worksheets in the same workbook. They have the same layout, and ranges.

Code:
=IF(A13<>"",INDEX('Name 1'!C5:C9000,MATCH('Search Result'!A13,'Name 1'!E5:E9000,0)), "")

I need the exact same thing, except with also a worksheet Name 2, and so forth, I been getting value errors and so forth.

 

[Pc1x1]

Anyone have any idea? Or the question is not clear? Thanks!

 

[Matty]

Is the formula working on the Worksheet 'Name 1'?

What happens with this formula:

=IF(A13<>"",INDEX('Name 2'!C5:C9000,MATCH(A13,'Name 2'!E5:E9000,0)),"")

Matty

 

[Pc1x1]

The problem Matty, is that I want the index to Index and match from the 3 worksheets. So the above formula doesn't work.

IE. When I type a Number, its is going to query first on Worksheet 1, then it queries worksheet 2, and so forth.

 

[Pc1x1]

Simply put, I need a Index and Match function to work using multiple sheets in the same workbook. Anyone have any ideas? Thanks!

 

[Matty]

How about:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},"",VLOOKUP(A13,CHOOSE({1,2},'Name 1'!E5:E9000,'Name 1'!C5:C9000),2,0),VLOOKUP(A13,CHOOSE({1,2},'Name 2'!E5:E9000,'Name 2'!C5:C9000),2,0)))

You can add to this formula, but it will slow your Workbook down considerably.

You'd be better consolidating the various Worksheets onto one, which would make data retrieval much more straightforward.

Matty

 

[Pc1x1]

I think that's the idea, but doesn't have to have such a large range.
Isn't there a way to keep it in the Index and Match Formula, but make it so it searches 3 worksheets instead of just one?

 

[Matty]

Are you looking to return numerical or text values?

If numerical, try:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},"",INDEX('Name 1'!C5:C9000,MATCH(A13,'Name 1'!E5:E9000,0)),INDEX('Name 2'!C5:C9000,MATCH(A13,'Name 2'!E5:E9000,0))))

If text, try:

=LOOKUP(REPT("z",255),CHOOSE({1,2,3},"",INDEX('Name  1'!C5:C9000,MATCH(A13,'Name 1'!E5:E9000,0)),INDEX('Name  2'!C5:C9000,MATCH(A13,'Name 2'!E5:E9000,0))))

Matty

 

[Pc1x1]

It's both Matty, thats why I wanted to keep the Index and Match, Worst comes to worst, I can make 3 Searches, but I would prefer to keep it all in one.