Need formula to return a value if in cell matches a cell in a range from another range or if not return 12

[jb00976]

I need a formula that will look at cell F6 which contains a name (format "Joe Smith") and compare that to a range C31:C53 which contains names, if the name matches I need it to return from the corresponding range K31:K53 (which contains number from 0 to 12 and includes figures with decimals), if it doesn't match I need the formula to return 12.

I tried:

=IF(F6=C31:D53,K31:L53,12)

but, it doesn't work and I am not sure why. I thought about using lookup which would work if the name matched something in the list but, wont if the name doesn't

Thanks in advance for any help

 

[Aladin Akyurek]

Unfortunately none of these formulas are working

the =IFERROR(INDEX(K31:K53,MATCH(F6,C31:C53,0)),"12") formula returns 12 every time

{=IF(COUNTIFS(C1:D53,"?*"),MAX(IF(MMULT(ISNUMBER(SEARCH(F6,C31:D53))+0,TRANSPOSE(COLUMN(C31:D53)^0)),IF(ISNUMBER(K31:L53),K31:L53,""),"")),12)} - returns 0 and says circular reference I noticed the first range had C1:D53 so I changed that to C31:D53 and then it returns 0 if the name in F6 isn't in the list in C31:C53 and returns correctly if it is in the list

You haven't clarified the issue of:

Is it (A) C31:C53 vs K31:K53 or (B) C31:D53 vs K31:K53.

If (A) is what you actually meant and F6 is the look up value which must be matched against C31:C53, then:

=IFERROR(INDEX(K31:K53,MATCH(F6,C31:C53,0)),12)​

should succeed and deliver 12 in case the match fails.

If (B), we have:

=IF(COUNTIFS(C31:D53,"?*"),MAX(IF(MMULT(ISNUMBER(SEARCH(F6,C31:D53))+0,TRANSPOSE(COLUMN(C31:D53)^0)),IF(ISNUMBER(K31:L53),K31:L53,""),"")),12)

which must be confirmed with control+shift+enter, not just with enter. I get 12, when F6 is not available in C31:D53. Note that the COUNTIFS bit checks for text values in C31:D53.
​