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

 

[mumps]

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

 

[Aladin Akyurek]

The formula you posted refer to ranges which are different from the ones in your description:

Either...

Control+shift+enter, not just enter:

=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)

Does this work for you?

Or...

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

 

[jb00976]

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

 

[mumps]

I tried the formula on some dummy data and it worked properly. Click here for the sample file. The formula in in cell A1.

 

[jb00976]

it works if the name is in the list but, when it isn't in the list it should return 12 and it returns 0

 

[DanteAmor]

Try this:

<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >C</td><td >F</td><td >G</td><td >H</td><td >K</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td style="background-color:#ffff00; ">Joe Smith</td><td style="background-color:#ffff00; text-align:right; ">3</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td >Joe Smith</td><td > </td><td > </td><td > </td><td style="text-align:right; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >32</td><td >Sue Pec</td><td > </td><td > </td><td > </td><td style="text-align:right; ">4</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >33</td><td >George Luc</td><td > </td><td > </td><td > </td><td style="text-align:right; ">5</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formula</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >G6</td><td >=IFERROR(VLOOKUP(F6,C31:K53,9,0),12)</td></tr></table></td></tr></table> <br /><br />

 

[mumps]

In the sample file I posted, I entered the letter "y" which is not in the range C31:C53 and it returned "12" in cell A1 as you requested. I don't understand why it returns zero for you.

 

[jb00976]

Got it working, thanks for your help!!

 

[DanteAmor]

You're welcome & thanks for the feedback.