# VLOOKUP Question

#### MSchiltz

##### New Member
I am trying to find a formula that recognizes the amount in column F (the amounts under the 00000) and finds it in the same row (different cell). Once it finds it in that row, I would like it to return the number in the same column row 2 (the 800 numbers).

Help?!?!

Possible?!?!

Thanks!

 Spend Down Expenses Network Account Vendor Invoice # P-Card Purchase Inv. Date 00000 890 891 895 Mileage a 15.98 15.98 555700 b 38.76 38.76 c 92.35 92.35 d 262.65 262.65 e 39.78 39.78 f 75.98 75.98 g 36.62 36.62 h 766.02 766.02 i 61.1 61.1 j 62.04 k 156.06 l 335.09 m 58.14 n 124.44 o 125.97 p 326.91 q 141.27 r 188.7 s 409.53 t 466.94 0

<tbody>
</tbody>

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
This isn't a place where vlookup will do what you want... You want to use a combination of Match() and Index(), as I'm assuming you want to return which of the "800" numbers match the decimal number:

Using your words that the column with 00000 is col F (I'm assuming the header is row 2), the formula would look something like the following (with only the visible portion of what you have):
=index(\$G\$2:\$I\$2,1,match(\$F3,\$G3:\$I3,0))

Hope this helps!

Last edited:
Rather than use a VLOOKUP, I just used a nested IF statement.

In Cell J3

Code:
``=IF(F3=G3,G\$2,IF(F3=H3,H\$2,IF(F3=I3,I\$2,"No Match")))``

Drag down as necessary. Will return "No Match" if there is not a number in any column.

This isn't a place where vlookup will do what you want... You want to use a combination of Match() and Index(), as I'm assuming you want to return which of the "800" numbers match the decimal number:

Using your words that the column with 00000 is col F (I'm assuming the header is row 2), the formula would look something like the following (with only the visible portion of what you have):
=index(\$G\$2:\$I\$2,1,match(\$F3,\$G3:\$I3,0))

Hope this helps!

This works great, and is probably the better solution. I'm not great with the INDEX and MATCH functions, but it appears to return the same results.
If you are like me, and hate errors in your spreadsheets, you could further it by using
Code:
``=IFERROR(INDEX(\$G\$2:\$I\$2,1,MATCH(\$F23,\$G23:\$I23,0)), "xxx")``
and replacing xxx with whatever you'd like.

While a nested IF could solve this, I'm assuming there is more to this spreadsheet than what was copied in, and that would get really messy, really quickly using that many IFs!

Wow! Learned some cool new tricks! The amount in column F is found in that same row and then pulls the 800 number. What happens if the amount in row F is a summation of two numbers in the same row (with different departments). It will return NA. Is there any way to adjust the formula to create to lines if column F is a summation of two numbers?

Thanks!

Replies
5
Views
686

1,203,524
Messages
6,055,907
Members
444,832
Latest member
bgunnett8

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