INDEX and looking for match in multiple columns

trekker1218

Board Regular
Joined
Feb 15, 2018
Messages
86
Office Version
  1. 2019
Platform
  1. Windows
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.
 
SHEET1
ABCDEFGHIJKL
1
800
A/C Equipment

<tbody>
</tbody>
46

<tbody>
</tbody>
660-2

<tbody>
</tbody>
MAC060-01-N

<tbody>
</tbody>
Multiaqua

<tbody>
</tbody>
2800
A/C Equipment

<tbody>
</tbody>
46

<tbody>
</tbody>
MAC-120HE

<tbody>
</tbody>
MAC-120HE-02

<tbody>
</tbody>
Multiaqua

<tbody>
</tbody>
388
Heating Equipment

<tbody>
</tbody>
34

<tbody>
</tbody>
PTP200SS0121SBAN

<tbody>
</tbody>
PTP200SS0121SBAN

<tbody>
</tbody>
Modine

<tbody>
</tbody>
488
Heating Equipment

<tbody>
</tbody>
34

<tbody>
</tbody>
PTP400SS0111

<tbody>
</tbody>
PTP400SS0111

<tbody>
</tbody>
Modine

<tbody>
</tbody>
588
Heating Equipment

<tbody>
</tbody>
34

<tbody>
</tbody>
PTP200SS0111

<tbody>
</tbody>
PTP200SS0111

<tbody>
</tbody>
Modine

<tbody>
</tbody>
688
Heating Equipment

<tbody>
</tbody>
34

<tbody>
</tbody>
PTP300

<tbody>
</tbody>
PTP300SS0111

<tbody>
</tbody>
Modine

<tbody>
</tbody>
788
Heating Equipment

<tbody>
</tbody>
34

<tbody>
</tbody>
PTP150SS0111

<tbody>
</tbody>
PTP150SS0111

<tbody>
</tbody>
660-2
Modine

<tbody>
</tbody>

<tbody>
</tbody>

SHEET2
ABCDEFGHI
1660-2
2PTP300

<tbody>
</tbody>
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,
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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.
 
Upvote 0
See if this works.

Excel Workbook
AB
1660-2Multiaqua1
2PTP300Modine4
3MAC-120HE-02Multiaqua2
4Test1Modine2
5PTP200SS0111Modine3
Sheet2
Excel Workbook
ABCDEFGHIJK
1800A/C Equipment46660-2MAC060-01-NMultiaqua1
2800A/C Equipment46MAC-120HEMAC-120HE-02PTP300Multiaqua2
388Heating Equipment34PTP200SS0121SBANPTP200SS0121SBANModine1
488Heating Equipment34PTP400SS0111PTP400SS0111Test1Modine2
588Heating Equipment34PTP200SS0111PTP200SS0111Modine3
688Heating Equipment34PTP300PTP300SS0111Modine4
788Heating Equipment34PTP150SS0111PTP150SS0111660-2Modine5
Sheet1
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top