INDEX and looking for match in multiple columns

T

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.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
It would help if you could post a small sample and the result you expect. Where is the result of the column you want to return. Is it the header?
Maybe the example below on ways to find the column for your INDEX will help.
If you have Excel ver. 2010 or later you can use the AGGREGATE function in cell B10 or the formula in B11 can be used in any ver., but it must be enter with CTRL-SHIFT-ENTER.
Excel Workbook
ABCDEF
1Test1Test2Test3Test4Test5
2Data30Data76Data73Data16Data15
3Data18Data56Data48Data27Data65
4Data96Data82Data24Data64Data53
5Data22Data62Data68Data20Data38
6Data54Data41Data75Data14Data28
7Data79Data87Data69Data78Data11
8
9FindData20
10Column #4
114
Sheet
 
Upvote 0
hello,

Sheet1
COL A Col B Col C Col D Col E

[TABLE="width: 841"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]HDS30AS0121[/TD]
[TD]HDS30AS0121[/TD]
[TD]Modine[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]HD125SS0121[/TD]
[TD]HD125SS0121[/TD]
[TD]Modine[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]HD75LP[/TD]
[TD]HD75LP[/TD]
[TD]Modine[/TD]
[/TR]
[TR]
[TD]PTS-350-AE-01-30[/TD]
[TD]PTS350SS0111SBAN[/TD]
[TD]PTS350SS0111SBAN[/TD]
[TD]TEST[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PTS175SS0111[/TD]
[TD]PTS175SS0111[/TD]
[TD]Modine[/TD]
[/TR]
</tbody>[/TABLE]



Sheet 2
COL A
PTS350SS0111SBAN


I am trying to Index a sheet with item# in Col A Then MATCH them to find the first match in above table COL A B C
I need it to NOT MATCH the blanks.

I want the Value of COL D when I find a MATCH in either COL A B C then return value of COL D

So In COL E
INDEX('Sheet1A:D,MATCH('Sheet 2A:A,'Sheet 1'!A:C,0),4) I know the last part is wrong. I need to find the first match in columns A B C

I hope this helps.



****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 841"]
<tbody>[TR]
[TD]PTS350SS0111SBAN[/TD]
[/TR]
</tbody>[/TABLE]
</body>
 
Upvote 0
Sheet 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[TD]Col E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD]HD125SS0121[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD]HD125SS0121[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 180"]
<tbody>[TR]
[TD]Modine[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 227"]
<tbody>[TR]
[TD]Equipment[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 182"]
<tbody>[TR]
[TD]HD75LP[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD]HD75LP[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 180"]
<tbody>[TR]
[TD]Modine[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 227"]
<tbody>[TR]
[TD]Equipment[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 239"]
<tbody>[TR]
[TD]PTS-350-AE-01-30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 182"]
<tbody>[TR]
[TD]PTS350SS0111SBAN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD]PTS350SS0111SBAN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 180"]
<tbody>[TR]
[TD]Modine[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD]Unit Heaters[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD]WS60/85[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD]WS60/85-1[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 117"]
<tbody>[TR]
[TD]Intertherm[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 227"]
<tbody>[TR]
[TD]Gases & Refrigerants[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C [/TD]
[TD]Col D [/TD]
[TD]Col E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 239"]
<tbody>[TR]
[TD]PTS-350-AE-01-30[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 168"]
<tbody>[TR]
[TD]PTS350SS0111SBAN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD]PTS350SS0111SBAN[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 239"]
<tbody>[TR]
[TD]HSB86SB01SA[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 168"]
<tbody>[TR]
[TD]PTS175SS0111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 240"]
<tbody>[TR]
[TD]PTS175SS0111[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need to get the result of Sheet 1 Col D By matching Sheet 2 Col A to Sheet 1 Col A,B,C
This works for trying to match to 2 columns

=Ifna(Index(Sheet1,A:E, Match(Sheet2,A:A,Sheet1A:A,0),4),(Index((Sheet1,A:E, Match(Sheet2,A:A,Sheet1B:B,0),4)))
If I try to add a 3rd Index/match to this formula I get no results.

The Value Im looking for can appear in multiple columns on sheet 1. I want to find the first match return result in COL E and stop.
 
Upvote 0
See if this works for you.

Excel Workbook
ABC
1PTS350SS0111SBANTESTUnit Heaters
Sheet2
Excel Workbook
ABCDE
1HDS30AS0121HDS30AS0121ModineEquipment
2HD125SS0121HD125SS0121ModineEquipment
3HD75LPHD75LPModineUnit Heaters
4PTS-350-AE-01-30PTS350SS0111SBANPTS350SS0111SBANTESTUnit Heaters
5PTS175SS0111PTS175SS0111ModineGases & Refrigerants
Sheet1
 
Upvote 0
The Formulas work but return a error window about calculating the formula.

error:
excel ran out of resources while attempting to calculate one or more formulas

I have to remove the Hyper Threading calculating in my advanced tab for it go away.

Thats fine but now its taking an hour to calculate 100 rows of data.
 
Upvote 0
When I ran the formulas on 1,000 rows it took less than 1 sec. When I referenced whole columns, like you had in post#1 it took about 2 sec. to run.
 
Upvote 0
AhoyNC said:
When I ran the formulas on 1,000 rows it took less than 1 sec. When I referenced whole columns, like you had in post#1 it took about 2 sec. to run.
Click to expand...
I was using entire columns. I will adjust to just the data i need and get back to you.
 
Upvote 0
I tried again and i keep getting that message. When I remove your formula. it goesa away. I have to try something else. Thank you for your efforts.
 
Upvote 0
AhoyNC,
I tried several different ways and still get that error.
However, I tested it on the sample data above and noticed another problem. Your formula does not stop at the first instance of the search value. It returns the last instance it finds in the array.

Is there any way to Index and match a value one column at a time for 3 columns in the same formula. Maybe an IF And/Or formula.
I need to search Col A for value. Stop if it finds a match to sheet2 A1 or try on Col B and stop or try Col C. Then once it finds a actual match it returns the value of column D.

I know I'm close but just missing something.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Index match match not using second set of match criteria
Replies
3
Views
77
Fluff
Fluff
J
  • Solved
Assistance with Index Match formula
Replies
2
Views
204
Jay88
J
L
  • Solved
Insert formulas to new lines in table
Replies
3
Views
154
Ltuk85
L
E
  • Question Question
Index with match and (maybe) offset?
2
Replies
10
Views
106
Peter_SSs
Peter_SSs
FatalLordes
  • Question Question
INDEX MATCH across more than one sheet?
2
Replies
10
Views
253
Alex Blakenburg
A

Forum statistics

Threads
1,223,952
Messages
6,175,594
Members
452,656
Latest member
earth

Share this page

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