Reverse Index & Match

[Blcook6383]

I need to match a value in an array to a value in the first row. See Below:


[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD]Medical[/TD]
[TD]Dental[/TD]
[TD]Voluntary[/TD]
[TD]Voluntary (2)[/TD]
[TD]Voluntary (3)[/TD]
[TD]Voluntary (4)[/TD]
[/TR]
[TR]
[TD]Medical[/TD]
[TD]Dental[/TD]
[TD][/TD]
[TD]Pediatric Dental[/TD]
[TD]Basic Life[/TD]
[TD]AFLAC[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]STD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]LTD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 808"]
<colgroup><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
For example, I want to be able to lookup "Pediatric Dental" and obtain "Voluntary (2)".

There are 2 caveats:
1 - "Pediatric Dental" can show up in other columns and rows below the first row (not just the second row of column four) and
2 - "Pediatric Dental" will only show in one place in the entire array below the first row.


I've used "=INDEX($C$43:$H$43,1,MATCH("Pediatric Dental",$C$44:$H$47,0))" and
"=INDEX($C$43:$H$43,,MATCH("Pediatric Dental",INDEX($C$44:$H$47,MATCH("Pediatric Dental",$C$44:$H$47,0),),0))"

but the problem is that I cannot match from an array it seems.

 

[Eric W]

As you surmised, MATCH doesn't work on a 2-D array. You can try something like this:

=INDEX($C$43:$H$43,AGGREGATE(15,6,(COLUMN($C$44:$H$47)-COLUMN($C$44)+1)/($C$44:$H$47="Pediatric Dental"),1))

 

[JamesonMH]

You could also use this. But like Eric's solution, if there's more than 1 occurrence of "Pediatric Dental", you're stuck. In this solution, the very last "1" below is the nth value for the SMALL function. So if there were say 2 occurrences and you wanted the last occurrence of Pediatric Dental, you could change the 1 to 2, etc.

=INDEX(C43:H43,0,SMALL(IF(C44:H47="Pediatric Dental",COLUMN(C43:H3)-MIN(COLUMN(C43:H3))+1),1))
Ctrl+Shift+Enter

There is an extensive thread on this very topic on mrexcel from 2010, which also offers VBA solutions. You may find that helpful for dealing with the multiple occurrences.

https://www.mrexcel.com/forum/excel-questions/516742-how-return-results-position-2d-array.html

James

 

[Blcook6383]

This is perfect as I am not supposed to have any duplicate entry.

 

[Eric W]

Glad we could help.