VLOOKUP not working with Column of Numbers

[pbassett]

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.

[Joe4]

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.

[pbassett]

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.

[pbassett]

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.

[Aladin Akyurek]

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) ?

[pbassett]

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

[Aladin Akyurek]

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.

[pbassett]

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

[Aladin Akyurek]

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.