formula with IF and LOOKUP not working correctly

[jb00976]

I have the following Formula:

=IF($D23="M/L",LOOKUP($F23,$M$12:$M$15,$N$12:$N$15))

Column D has values of "Hybrid", "Small" and "M/L"

Column F has values of "G7", "G8", "G9" and "G10"

M12:M15 and N12:N15 looks like this:

[TABLE="class: grid, width: 500"]
<colgroup><col style="width:88pt" width="117" span="2"> </colgroup><tbody>[TR]
[TD="width: 117"]G7
[/TD]
[TD="width: 117"]$40,000[/TD]
[/TR]
[TR]
[TD="width: 117"]G8[/TD]
[TD="width: 117"]$50,000
[/TD]
[/TR]
[TR]
[TD="width: 117"]G9[/TD]
[TD="width: 117"]$60,000[/TD]
[/TR]
[TR]
[TD="width: 117"]G10[/TD]
[TD="width: 117"]$75,000[/TD]
[/TR]
</tbody>[/TABLE]

For some reason when the value in column F is G10 it is returning "#N/A" instead of $75,000. When the value is G7, G8 or G9 the formula works.

Please help!

Thank you

 

[Scott T]

Lookup only does approximant match which requires the table to be sorted. If your table your get

Book1
	M	N
12	G10	$75,000
13	G7	$40,000
14	G8	$50,000
15	G9	$60,000
Sheet1

And then you get 75,000 from you lookup formula.

If you do not want to sort the range you can use. This will be an exact match lookup so if the value is not found it will hive a N/A error

=INDEX(N12:N15,MATCH(F23,M12:M15,0))

 

[DanteAmor]

How about:

<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;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >D</td><td >F</td><td >G</td><td >M</td><td >N</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><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </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; " >12</td><td > </td><td > </td><td > </td><td > </td><td >G7</td><td style="text-align:right; ">$40,000</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td > </td><td > </td><td >G8</td><td style="text-align:right; ">$50,000</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td > </td><td > </td><td > </td><td >G9</td><td style="text-align:right; ">$60,000</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td > </td><td > </td><td > </td><td >G10</td><td style="text-align:right; ">$75,000</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </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; " >22</td><td > </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; " >23</td><td > </td><td >M/L</td><td >G10</td><td style="text-align:right; ">$75,000</td><td > </td><td > </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 >G23</td><td >=IF(D23="M/L",VLOOKUP(F23,M12:N15,2,0))</td></tr></table></td></tr></table> <br /><br />

 

[jb00976]

Thank you for your help I was able to get it to work with the VLOOKUP formula

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.