VLOOKUP in Excel

KiTBoH · 15 Apr 2009 at 21:21

I have an Excel worksheet, and I'm trying to lookup a 2 digit number (01, 53, 14) and then bring up a date from the second column.
It works fine when I search text. I entered abc into the table and when I searched it it came up with the correct date. It just won't work with numbers.

This is what I have so far

=VLOOKUP(B7,Sheet3!A4:B30,2,FALSE)

B7 is the number I am looking for.

A4:B30 The table that I am searching in. 2 is the column with the date. For Example

A B
01 February 2012
03 March 2003
04 June 2004

Any suggestions on what I could do to make it all work?

THanks

SourChipmunk · 15 Apr 2009 at 21:35

Put B7 in quotes, I think. Or an apostrophe before. I forget which.

edit: scratch that. I thought you were looking for B7. I didn't read carefully. Never mind. Hold on.

KiTBoH · 15 Apr 2009 at 21:58

Raist said:
Put B7 in quotes, I think. Or an apostrophe before. I forget which.

edit: scratch that. I thought you were looking for B7. I didn't read carefully. Never mind. Hold on.

Yeah, B7 is a cell with data that is input to it using a formula. I want to look up the result that was then put into B7.
The problem is caused because the result is a number, and for some reason it won't look up the numbers.

Hamish · 15 Apr 2009 at 21:59

Are both columns formatted as the same type? i.e. both either text or number.

KiTBoH · 15 Apr 2009 at 22:21

Hamish said:
Are both columns formatted as the same type? i.e. both either text or number.

No, one's a number and one is text.

Would that be the problem?

suarve · 16 Apr 2009 at 09:12

If you just posted your excel file I'm sure someone would spot the problem straight away.

KiTBoH · 16 Apr 2009 at 13:50

Does the VLOOKUP command search through the entire table, or just the left column?

Hamish · 16 Apr 2009 at 13:52

KiTBoH said:
No, one's a number and one is text.

Would that be the problem?

I'd try making them both the same type

Meeko · 16 Apr 2009 at 14:13

You'll need to ensure that the format of cell B7 is the same as the data in Column A of your lookup table.

If one is set to text and one number, then it won't work.

KiTBoH · 16 Apr 2009 at 14:25

OK I know what the problem I am having is now.
I am using =MID(a1,2,2) to get the number that I am looking up.
It wont look up the number that is then given out.

How can I make it do that?

suarve · 16 Apr 2009 at 16:25

KiTBoH said:
OK I know what the problem I am having is now.
I am using =MID(a1,2,2) to get the number that I am looking up.
It wont look up the number that is then given out.

How can I make it do that?

If I'm understanding you correctly, you'll need to put that mid function inside the vlookup function for the lookup cell...if that makes sense.

KiTBoH · 16 Apr 2009 at 21:41

suarve said:
If I'm understanding you correctly, you'll need to put that mid function inside the vlookup function for the lookup cell...if that makes sense.

How would I do that?

I tried =VLOOKUP(MID(A3,3,2),Sheet2!B8:C19,2) but that didn't work :/

ArmyofHarmony · 18 Apr 2009 at 12:58

Tried pressing F4 when you select your lookup table? to make it absolute referencing

Cogwork · 18 Apr 2009 at 13:33

You probably know this already - but VLOOKUP gets borked if the values are in alphabetical/numeric order. Even one entry out of place will give bugged results.

KiTBoH · 18 Apr 2009 at 18:04

I managed to get the number table to work, by putting the Mid part inside the VLOOKUP.

Thanks for the help

xirokx · 23 Apr 2009 at 21:51

use INDEX MATCH its much better and the data doesnt have to be sorted numerically for it to work...

formula is:-

=index(the answer, first column to lookup, second column to lookup, 0),1) hit enter

its so easy when you get the hang of it like anything really