USING THE VLOOKUP FUNCTION

ELIZABETH OWENS

New Member
Joined
Jan 20, 2005
Messages
1
Seems that this function is working fine for a list when the list consists of alpha numeric or all alpha characters. However when I have only a number in the list, the lookup function cannot make the connection between list a and list b.

For example:

72QM592M
82QM5379M
8083397
8083398
FOXU118138
123456777

This is the List A and List B would have these items in it with other data that I would want to pull into a spreadsheet with the lookup function. The data comes in fine for the entries with the Alpha-numeric entries but not with the numeric entries only.

Can anyone help with the question as to why the lookup function is not working for both?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

martinee

Well-known Member
Joined
Nov 4, 2003
Messages
960
Could you post the formula you are using? Have you implemented the -- before your ranges, so excel uses the most relevent cell format for the data it contains? HTH.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Welcome to the Board.

It's because the entries in your lookup table are text and the item to be looked up is a number (or vice versa).

You can quickly convert text entries that look like numbers to true numbers by selecting all the data, choosing Data|Text to Columns from the menu and clicking finish.

Or you can use a formula like this:

=VLOOKUP(TEXT(A1,"@"),$D$1:$E$4,2,FALSE)
 

bluemonster

New Member
Joined
Aug 3, 2004
Messages
7
Don't know why u can't get it to work, but I used those you had in list A and it worked fine.?????
 

Forum statistics

Threads
1,147,691
Messages
5,742,661
Members
423,746
Latest member
Joaogomes

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top