VLOOKUP not working with Column of Numbers

pbassett

Active Member
Joined
May 5, 2004
Messages
358
I am pulling my hair out trying to get a simple VLOOKUP function to work. I have a sheet that was copy/pasted from an MS-Access query.

I think something is very wrong. The cells in the look-up sheet "Reference" are numbers, but the Format is General - their values are "261", "1417", "10055", etc.

My cell A1 has the value "10055" and has Format of General. But
=A1='Reference'!A5 comes back FALSE, which is not at all what I expected. However,
=A1='Reference'!A5+0 comes back TRUE. So I'm very confused :(

My ultimate goal is of course to get
= VLOOKUP(A1,'Reference'!A:C,3,FALSE)
to work, so I can retrieve from Col C what matches from Col A. Can I put "+0" anywhere in the 'Reference' side of the equation to convert the look-up column to numerics? I really didn't think I'd hav eto do that!

Of course, VLOOKUP works when Character Strings are in the look-up Column, but these "numbers" are causing havoc.

Ultimately, instead of A1, I will be look up via the sheet name, which will be a number, like 10055, so I really need
=VLOOKUP(RIGHT(CELL("FILENAME",A1),LEN(CELL("FILENAME",A1))-FIND("]",CELL("FILENAME",A1))), 'Reference'!A:C, 3, FALSE) to work.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
If you are copying and pasting from an Access query, it will usually paste everything as text, evem numbers (at least mine does).

Highlight the column of numbers, change the format to General, then select "Text to Columns" from the Data menu and click finish. This should convert all those numbers in Text format to Numeric format.

Then the VLOOKUP should work fine.
 
Upvote 0
Thanks - I'm getting close. I did the Data->Text to Columns tip and now this returns TRUE:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)+0='Reference'!A2

which takes my Sheet Name, then converts to numeric via "+0".

Unfortunately, this returns "VLOOKUP=":
="VLOOKUP=" & VLOOKUP(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)+0,'Reference'!A:C,3,FALSE)

not sure why the VLOOKUP would not return the matching lookup.
 
Upvote 0
Actually, I got it to work using the trusty INDEX/MATCH combo:

=INDEX('Reference'!C:C,MATCH(1,('Scope Reference'!$A$1:$A$9999=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)+0)*(1=1),0))

for some Reason INDEX/MATCH never lets me down with Numeric lookups, wheareas VLOOKUP can cause aggravation. Plus, MATCH is more forgiving in case someday the lookup column is not sorted.
 
Upvote 0
pbassett said:
Actually, I got it to work using the trusty INDEX/MATCH combo:

=INDEX('Reference'!C:C,MATCH(1,('Scope Reference'!$A$1:$A$9999=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)+0)*(1=1),0))

for some Reason INDEX/MATCH never lets me down with Numeric lookups, wheareas VLOOKUP can cause aggravation. Plus, MATCH is more forgiving in case someday the lookup column is not sorted.

Strange formula...

What is a typical result of..

MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) ?
 
Upvote 0
It returns the sheet name, like "10055" without the filename, pathname, etc. I need just the sheet name, since I use this as a unique identifier for looking up data from Master Sheets. Actually, that brings me to my next question.

I would like to write a custom formula MySheetName to save some space in these cell formulas. This is my first attempt at writing a function, but unfortunately it returns #VALUE :(

====
Function ThisSheetName() As String
ThisSheetName = Application.WorksheetFunction.Mid(Application.WorksheetFunction.CELL("filename", A1), Application.WorksheetFunction.Find("]", Application.WorksheetFunction.CELL("filename", A1)) + 1, 255)
End Function
====
 
Upvote 0
pbassett said:
It returns the sheet name, like "10055" without the filename, pathname, etc. I need just the sheet name, since I use this as a unique identifier for looking up data from Master Sheets...

If that's case...

Change:

=INDEX('Reference'!C:C,MATCH(1,('Scope Reference'!$A$1:$A$9999=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)+0)*(1=1),0))

to:

=INDEX('Reference'!C:C,MATCH(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)+0,'Scope Reference'!$A:$A,0))

BTW, are 'Reference' and 'Scope Reference' different sheets? If not, you'll need to align them.
 
Upvote 0
Thanks! I tried to simplify my question by changing 'Scope Reference' to 'Reference', but i didn't fix allreferences!

I thought it was lame to add the (1=1) in my MATCH but that's because the first argument was 1.

If you can help 1 more time, do you have any tips on the Function? I'm sure it's easy.

Thanks again,
Pete
 
Upvote 0
pbassett said:
Thanks! I tried to simplify my question by changing 'Scope Reference' to 'Reference', but i didn't fix allreferences!

I thought it was lame to add the (1=1) in my MATCH but that's because the first argument was 1.

MID(...)+0 effects the necessary coercion from text-number to real number, so the *(1=1) bit superfluous.

If you can help 1 more time, do you have any tips on the Function? I'm sure it's easy...

Pete, the latter requires a VBA programmer.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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
Back
Top