Find first match from one range in another range and return related value

cparsons1

New Member
Joined
Jan 18, 2019
Messages
3
I need to find the first instance of a named range that occurs in a table and return another value from that table on the same row.

Two Named Ranges Cat1 and Cat2

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Cat 1[/TD]
[TD="width: 64"]Cat 2[/TD]
[/TR]
[TR]
[TD]Type A[/TD]
[TD]Type E[/TD]
[/TR]
[TR]
[TD]Type B[/TD]
[TD]Type F[/TD]
[/TR]
[TR]
[TD]Type C[/TD]
[TD]Type G[/TD]
[/TR]
[TR]
[TD]Type D[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need to search Column A in this table and return the first time something from Cat1 appears and return the value from column B that corresponds to the match. I have looked through many examples of Index Match but I have not been able to find this scenario.

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Type[/TD]
[TD]Volume[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Type E[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Type G[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Type C[/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Type F[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Type F[/TD]
[TD="align: right"]600[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Type F[/TD]
[TD="align: right"]700[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Type F[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Type F[/TD]
[TD="align: right"]900[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]Type A[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]Type G[/TD]
[TD="align: right"]1100[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]Type G[/TD]
[TD="align: right"]1200[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD]Type G[/TD]
[TD="align: right"]1300[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]Type G[/TD]
[TD="align: right"]1400[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD]Type G[/TD]
[TD="align: right"]1500[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Welcome to the forum.

Try:


ABCDEFGHI
1st Match
Cat_1

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Type[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Volume[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Cat 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Cat 2[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Cat[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Type E[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]200[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Type A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Type E[/TD]
[TD="align: right"][/TD]

[TD="align: right"]400[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Type G[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]300[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Type B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Type F[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Cat_2[/TD]
[TD="align: right"]200[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Type C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]400[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Type C[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Type G[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Type F[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]500[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Type D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Type F[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]600[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Type F[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]700[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Type F[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]800[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Type F[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]900[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]Type A[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Type G[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Type G[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1200[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Type G[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Type G[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]1400[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Type G[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet21

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=IFERROR(INDEX($B$2:$B$15,AGGREGATE(15,6,MATCH(INDIRECT(G2),$A$2:$A$15,0),1)),"No match")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Note that the D2:D5 is Cat_1, and E2:E4 is Cat_2. Without the underscores, those are invalid names in more recent versions of Excel. This raises another point, since the AGGREGATE function is also only available since version 2010. But it's possible to do it without AGGREGATE if need be.

Hope this helps.
 
Upvote 0
How about


Excel 2013/2016
ABCDEFG
1TypeVolumeCat 1Cat 2
2Type E200400Type AType E
3Type G300Type BType F
4Type C400Type CType G
5Type F500Type D
6Type F600
7Type F700
8Type F800
9Type F900
10Type A1000
11Type G1100
12Type G1200
13Type G1300
14Type G1400
15Type G1500
End
Cell Formulas
RangeFormula
D2{=INDEX(B2:B15,MATCH(1,--(A2:A15=F2:F5),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Fluff, I don't believe that will work if the first match is more than 4 rows down. For example, if A4 were "Type X", then I'd expect the result to be 1000, since A10 has "Type A" in it. In fact, I think it only coincidentally works since "Type C" is on the same row in both ranges. You'd need to do something like A2:A15=TRANSPOSE(F2:F5) to check everything against everything, then use MMULT to roll up the results.
 
Last edited:
Upvote 0
Thanks! I'm trying this but where does the 15,6 come from?
Sheet21

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]H2[/TH]
[TD="align: left"]=IFERROR(INDEX($B$2:$B$15,AGGREGATE(15,6,MATCH(INDIRECT(G2),$A$2:$A$15,0),1)),"No match")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Note that the D2:D5 is Cat_1, and E2:E4 is Cat_2. Without the underscores, those are invalid names in more recent versions of Excel. This raises another point, since the AGGREGATE function is also only available since version 2010. But it's possible to do it without AGGREGATE if need be.

Hope this helps.[/QUOTE]
 
Upvote 0
I've been trying this but it returns a 0 (zero). The Cat1 range is actually a named range from another sheet and is in AY7:AY41. Also, I'm not familiar with the use of --.
 
Upvote 0
AGGREGATE is one of the most complicated functions, since it can do 19 different functions. The 15 says which function to do, in this case the SMALL function. The 6 means to ignore errors. The MATCH function looks for every value in Cat_1 in the range A2:A15. If it finds it, it returns the position. If it doesn't, it returns #N/A, so the 6 is needed to ignore the #N/A values. Incidentally, the non-AGGREGATE version is:

=IFERROR(INDEX($B$2:$B$15,SMALL(IFERROR(MATCH(INDIRECT(I2),$A$2:$A$15,0),2^21),1)),"No match")

which you have to enter using Control+Shift+Enter.

See this link for more about AGGREGATE:
https://support.office.com/en-us/article/aggregate-function-43b9278e-6aa7-4f17-92b6-e19993fa26df
 
Upvote 0
Here's an alternate formula based on Fluff's idea:

=INDEX(B2:B15,MATCH(1,--(MMULT(--(A2:A15=TRANSPOSE(F2:F5)),ROW(F2:F5))>0),0))
confirmed by pressing Control+Shift+Enter.

But see if you get one of the others working first, since I think they'd be more efficient.
 
Upvote 0
Fluff, I don't believe that will work if the first match is more than 4 rows down. For example, if A4 were "Type X", then I'd expect the result to be 1000, since A10 has "Type A" in it. In fact, I think it only coincidentally works since "Type C" is on the same row in both ranges. You'd need to do something like A2:A15=TRANSPOSE(F2:F5) to check everything against everything, then use MMULT to roll up the results.
Thanks for that Eric, you're quite right.
:beerchug:
 
Upvote 0

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

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