INDEX and looking for match in multiple columns

[trekker1218]

Hello,
I am trying to use Index:Match to find a value in several different columns then return the result of a column once I find a match. Here is the sample formula I am trying.
=IFNA(INDEX('ALL-CIMM-DATA-321'!A:I,MATCH('RAW-DATA'!K:K,'ALL-CIMM-DATA-321'!I:I,0),4),(INDEX('ALL-CIMM-DATA-321'!A:J,MATCH('RAW-DATA'!K:K,'ALL-CIMM-DATA-321'!J:J,0),4),(INDEX('ALL-CIMM-DATA-321'!A:J,MATCH('RAW-DATA'!K:K,'ALL-CIMM-DATA-321'!H:H,0),4))))

It works if I use 2 columns but not more than that.

thank you for any help in advance.

 

[trekker1218]

SHEET1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]800
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD]A/C Equipment[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="align: right"]46[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD]660-2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD]MAC060-01-N[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 180"]
<tbody>[TR]
[TD]Multiaqua[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]800[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD]A/C Equipment[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="align: right"]46[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD]MAC-120HE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD]MAC-120HE-02[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 180"]
<tbody>[TR]
[TD]Multiaqua[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]88[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD]Heating Equipment[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="align: right"]34[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD]PTP200SS0121SBAN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD]PTP200SS0121SBAN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 180"]
<tbody>[TR]
[TD]Modine[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]88[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD]Heating Equipment[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="align: right"]34[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD]PTP400SS0111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD]PTP400SS0111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 180"]
<tbody>[TR]
[TD]Modine[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]88[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD]Heating Equipment[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="align: right"]34[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD]PTP200SS0111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD]PTP200SS0111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 180"]
<tbody>[TR]
[TD]Modine[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]88[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD]Heating Equipment[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="align: right"]34[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD]PTP300[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD]PTP300SS0111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 180"]
<tbody>[TR]
[TD]Modine[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]88[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD]Heating Equipment[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 59"]
<tbody>[TR]
[TD="align: right"]34[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD]PTP150SS0111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD]PTP150SS0111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]660-2[/TD]
[TD][TABLE="width: 180"]
<tbody>[TR]
[TD]Modine[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

SHEET2[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]660-2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]PTP300[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
this is sample of actual data.
I need to find(sheet2 (a1) in (sheet1 col H, I, J) then return value of (Sheet1 A1)
I need the search to stop once it finds the 660-2 value in col H first, if not then Col I if not then Col J

to test it I changed the value on sheet1 h1 to blank and it changes the result from 800 to 88

i hope this makes sense.

thanks,

 

[fritzkie_alexander]

Hi,

Is this what you need ==> =INDEX(Sheet1!$A$1:$A$7,LOOKUP(2,1/(SEARCH(Sheet1!$H$1:$J$7,Sheet2!$A1)),ROW(Sheet1!$H$1:$J$7)))

 

[trekker1218]

Almost...
Change the value in sheet 1 H2 to 660-2-4 and you will see your formula did not return the correct value of 88 because the search of 660-2 should be found in row 7 col J

Also, it does not work if it hits a blank cell.

 

[AhoyNC]

See if this works.

Excel Workbook
	A	B
1	660-2	Multiaqua1
2	PTP300	Modine4
3	MAC-120HE-02	Multiaqua2
4	Test1	Modine2
5	PTP200SS0111	Modine3
Sheet2

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	800	A/C Equipment	46					660-2	MAC060-01-N		Multiaqua1
2	800	A/C Equipment	46					MAC-120HE	MAC-120HE-02	PTP300	Multiaqua2
3	88	Heating Equipment	34					PTP200SS0121SBAN	PTP200SS0121SBAN		Modine1
4	88	Heating Equipment	34					PTP400SS0111	PTP400SS0111	Test1	Modine2
5	88	Heating Equipment	34					PTP200SS0111	PTP200SS0111		Modine3
6	88	Heating Equipment	34					PTP300	PTP300SS0111		Modine4
7	88	Heating Equipment	34					PTP150SS0111	PTP150SS0111	660-2	Modine5
Sheet1

 

[trekker1218]

Works Great ! Woo Hoo.

Thank You very much.

 

[AhoyNC]

you're welcome. Thanks for the feedback.

 

[Drrellik]

If you never got this try IFNA(IFNA(IFNA( index(MATCH()) where you evaluate the first column for your value when it returns an NA it will evaluate the next column and so on you can nest as many NA's as you need for as many columns.

=IFNA(IFNA(IFNA(INDEX(Phone!$B$3:$F$30,MATCH(B2,Phone!$B$3:$B$30,0),2),INDEX(Phone!$H$3:$M$30,MATCH(B2,Phone!$H$3:$H$30,0),2)),INDEX(Phone!$O$3:$S$30,MATCH(B2,Phone!$O$3:$O$30,0),2)),INDEX(Phone!$U$3:$Y$30,MATCH(B2,Phone!$U$3:$U$30,0),2))

this is a working line out of my sheet where the employees are listed on one tab and their phone numbers are listed on the tab called Phone and I used employee numbers to locate the right employee and return the data I wanted based on the column