Match a Row and then return a value from a match in the Row. (INDEX-MATCH-MATCH?)

Hi all,

I'm struggling to solve the following, struggle a bit and would be glad for your help:




I've got a Table with data as below (sorry. not allowed to post links or pictures or attachements):

Please Login or Register  to view this content.

Objective:

• Lookup from a table
• For a given ID (from a fixed column) and determine Row
• Look up from cultiple Categories (Cat1 to Cat10) the Value where the Name matches "Money in $"

I know how to get the value for a given row with INDEX and MATCH:

Please Login or Register  to view this content.

How can I make this happen (ideally in one expression without helper cells) to Make that expression finf the right row machtig an ID e.g. 12345 and does the Match-Category on that row?

Thanks a lot in advance

Try this in C9:

=IFERROR(INDEX(2:4,MATCH(B9,A2:A4,0),1/(1/(MIN(IF((A2:A4=B9)*(C2:L4="Money in $"),COLUMN(C2:L4)))))+1),"No Match") Ctrl Shift Enter

Originally Posted by 63falcondude

=IFERROR(INDEX(2:4,MATCH(B9,A2:A4,0),1/(1/(MIN(IF((A2:A4=B9)*(C2:L4="Money in $"),COLUMN(C2:L4)))))+1),"No Match") Ctrl Shift Enter

Thanks big times! Works like charm.

I need to digest that not being that savvy in Excel Arrays. Probalby could do with some high level explanantion, especially on what the 1/(1/ is and what it is doing. What would be the subjcet I look for in the internet to learn more about!

Again, thanks do much. You nailed it! And not to forget about Glenn! thanks to both of you. (The speed at which you did is almost embarrassing to me :-))

Happy to help. Thanks for the rep guys!

The MIN(IF part of the formula returns the column number that "Money in $" is found in.

For example, when B9=23456, =MIN(IF((A2:A4=B9)*(C2:L4="Money in $"),COLUMN(C2:L4))) returns 7. "Money in $" is found in column G, which is the 7th column.

The 1/1/ part of the formula was put into place for instances where B9 is not found in A2:A4.
For example, when B9=34567, =MIN(IF((A2:A4=B9)*(C2:L4="Money in $"),COLUMN(C2:L4))) returns 0.
The 1/1/ is there to create an error when the MIN(IF part returns 0 and return the column number whenever it is not 0.

For instance, when it returns 7 we have 1/(1/7) which is 7. When it returns 0 we have 1/(1/0) which returns a #DIV/0! error.

The #DIV/0! error will make the entire formula return an error, which is what we want when the MIN(IF part is 0.
Now the IFERROR part of the formula returns "No Match" instead of displaying the error.