help with a formula please

[fastbuck]

Hi all
Hoping I can get some help with a formula that will do the following.

If the number in cell M9 is equal to a number in cell range L9:L28 then return the cell value beside from cell range K9:K28.
Examples as follows
If M9 equals L12 return K12
If M9 equals L23 return K23

Thanks
N

 

[bbott]

=IFERROR(INDEX($K$9:$K$28,MATCH(M9,$L$9:$L$28,0)),"No Match")

 

[fastbuck]

Hi bbot. Thanks for your reply. Needs to do the following. Column M will be manual entry.

K L M N
$3.40 17 1 $4.60
$3.50 6 5 $9.50
$4.60 1
$7.30 12
$9.50 5
$11.90 4
$19.20 20

 

[bbott]

Yes, that's what my formula does**. Is it not working for you? And if not, are you getting an incorrect answer or an error?

**Make sure you used the INDEX formula that I posted. I originally posted a VLOOKUP solution that won't work for your data.

 

[fastbuck]

That didn't turn out the way I posted it How do I attach a sample spreadsheet??

 

[fastbuck]

it says no match

 

[bbott]

it says no match

Check for leading or trailing spaces in your data. If there are spaces, it won't match.

Also, are any of your numbers formatted as text?

 

[bbott]

See if this version works:

=IFERROR(INDEX($K$9:$K$28,MATCH(TRIM(M9),$L$9:$L$28,0)),"No Match")

 

[fastbuck]

the numbers in columns k & L are called from another sheet eg: =Sheet_2!F7. Would that make a difference?

 

[fastbuck]

your second version gives the same result.
the numbers in columns k & L are called from another sheet eg: =Sheet_2!F7. Would that make a difference?