How to use index match to search for a value from multiple sheets

[mcf]

Hi there,

I have a set of values in column A in sheet 1 and a compilation of some of the same set of data in column A in sheet 2. Using the index match function, I have been able to list the corresponding values in column B of sheet 2 onto column B of sheet 1 (for those values which are common to both sheets - matching occurs between column A in both sheets). However, I now have some of the set of data in sheet 2 and the rest in sheet 3 (again, in column A). Is there any way of using index match to search in both sheets and index the corresponding value from column B of the sheet containing the matched value in column B of sheet 1?

I have tried this by nesting the index match functions for each sheet into two separate IF arguments but haven't had any luck. Is there a better way of doing this?

This is what I have currently tried in Cell B2 of sheet 1:

=IF(INDEX(Sheet2!$B$2:$B$3001,MATCH(A2,Sheet2!$A$2:$A$3001,FALSE),1),IF(INDEX(Sheet3!$B$2:$B$3001,MATCH(A2,Sheet3!$A$2:$A$3001,FALSE),1)))

Excel keeps saying this formula has errors but I have no idea as to what changes I need to make to get the formula to model my situation. Then again, I'm not even sure if this is the approach I should be taking to create a formula that models my situation.

Anyway, thanks for your help. I very much appreciate it.

-mcf

 

[Robert Mika]

Hello and welcome
Try this:
Excel 2010

	A	B
1	name	values
2	a	1

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Excel 2010

	A	B
1	name	values
2	b	2
3

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Excel 2010

	A	B
1
2	name	values
3	a	1
4	b	2
5
6
7

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Summary​

Array Formulas Cell Formula B3 {=VLOOKUP(A3,INDIRECT("sheet"&MATCH(TRUE,COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:3"))&"!a2:a100"),A3)>0,0)&"!a2:b100"),2,0)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>