# help with a formula please

#### fastbuck

##### Board Regular
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

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
=IFERROR(INDEX(\$K\$9:\$K\$28,MATCH(M9,\$L\$9:\$L\$28,0)),"No Match")

Last edited:
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

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.

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

it says no match

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?

See if this version works:

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

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

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?

Replies
1
Views
172
Replies
3
Views
208
Replies
3
Views
127
Replies
7
Views
75
Replies
4
Views
717

1,203,326
Messages
6,054,747
Members
444,748
Latest member
knowak87

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back