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