Index Match + UDF

[Wheelie686]

I have the following UDF to get the first colored cell in a range. What I'd like to do is find the first colored cell in a range but use Index Match to determine the range/row to look in.

Q2:Q12 has the numbers I want to match from Column B
I've manually added the answers into R2:R12
S2 has an example of the Function below.

Does anyone know if that's possible? If it can be done without the UDF, that would be even better but I suspect not.

Please Login or Register  to view this content.

[Sam Capricci]

This appears to work w/o a UDF...
=IF(VLOOKUP(Q2,$B$2:$C$40,2,FALSE)<>"X",VLOOKUP(Q2,$B$2:$C$40,2,FALSE),IF(VLOOKUP(Q2,$B$2:$D$40,3,FALSE)<>"X",VLOOKUP(Q2,$B$2:$D$40,3,FALSE),VLOOKUP(Q2,$B$2:$E$40,4,FALSE)))

[Sam Capricci]

BTW, I'm not crazy about using table names so I just used the cell addresses but they do refer to your table areas.

[Wheelie686]

Hey Sam,
I appreciate your answer but I guess I should've mentioned that the letters will all be X's - it's basically a chart to indicate what skills people have but I just removed the sensitive information. The only reason I used different letters was so I could tell if it was giving me the right cell.

[Phuocam]

Try this in R2

=FindCellOfColor(INDEX($C$2:$G$40,MATCH(Q2,$B$2:$B$40,0),0),3)

[Wheelie686]

Ah there we go! Thanks! I was racking my brain on getting the right cell references and syntax. Much appreciated!