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

[cparsons1]

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

Cat 1	Cat 2
Type A	Type E
Type B	Type F
Type C	Type G
Type D

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

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.

	A	B
1	Type	Volume
2	Type E	200
3	Type G	300
4	Type C	400
5	Type F	500
6	Type F	600
7	Type F	700
8	Type F	800
9	Type F	900
10	Type A	1000
11	Type G	1100
12	Type G	1200
13	Type G	1300
14	Type G	1400
15	Type G	1500
16
17
18
19
20

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Eric W]

Welcome to the forum.

Try:

	A	B	C	D	E	F	G	H	I
1	Type	Volume		Cat 1	Cat 2		Cat	1st Match
2	Type E	200		Type A	Type E		Cat_1	400
3	Type G	300		Type B	Type F		Cat_2	200
4	Type C	400		Type C	Type G
5	Type F	500		Type D
6	Type F	600
7	Type F	700
8	Type F	800
9	Type F	900
10	Type A	1000
11	Type G	1100
12	Type G	1200
13	Type G	1300
14	Type G	1400
15	Type G	1500

<tbody>
</tbody>

Sheet21​

Worksheet Formulas Cell Formula H2 =IFERROR(INDEX($B$2:$B$15,AGGREGATE(15,6,MATCH(INDIRECT(G2),$A$2:$A$15,0),1)),"No match") <tbody> </tbody>

<tbody>
</tbody>

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.

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G
1	Type	Volume				Cat 1	Cat 2
2	Type E	200		400		Type A	Type E
3	Type G	300				Type B	Type F
4	Type C	400				Type C	Type G
5	Type F	500				Type D
6	Type F	600
7	Type F	700
8	Type F	800
9	Type F	900
10	Type A	1000
11	Type G	1100
12	Type G	1200
13	Type G	1300
14	Type G	1400
15	Type G	1500
End

Cell Formulas
Range	Formula
D2	{=INDEX(B2:B15,MATCH(1,--(A2:A15=F2:F5),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Eric W]

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.

 

[cparsons1]

Thanks! I'm trying this but where does the 15,6 come from?

Sheet21​

Worksheet Formulas Cell Formula H2 =IFERROR(INDEX($B$2:$B$15,AGGREGATE(15,6,MATCH(INDIRECT(G2),$A$2:$A$15,0),1)),"No match") <tbody> </tbody>

<tbody>
</tbody>

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]

 

[cparsons1]

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 --.

 

[Eric W]

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

 

[Eric W]

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.

 

[Fluff]

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.