excel - vllookup function

[Guest]

I entered a vllookup function in one of my spreadsheets. I then copied it
down the column, but everyonce in a while the lookup didn't work & came up
with N/A. Yet all the items were in the data range. Why would it not be
consistent???

 

[Guest]

I entered a vlookup function in one of my spreadsheets. I then copied it
down the column, but everyonce in a while the lookup didn't work & came up
with N/A. Yet all the items were in the data range. Why would it not be
consistent???

Probably because the data within the lookup col in the vlookup's table_array
and the lookup values may not be consistent. For example: If it's numbers
being matched, these need to be consistent: either match text numbers vs text
numbers, or real numbers vs real numbers (lookup values vs lookup col in
table _array)

So instead of say:
=VLOOKUP(A2,Sheet2!A:B,2,FALSE)

Try it as:
=VLOOKUP(A2+0,Sheet2!A:B,2,FALSE)
The "+0" is one way to coerce the text lookup number in A2 to a real number

Or, try:
=VLOOKUP(A2&"",Sheet2!A:B,2,FALSE)
to convert the real number in A2 to a text number
so that it will match the text numbers in the lookup col of the table_array

Or, try something like:
=VLOOKUP(TEXT(A2,"0000"),Sheet2!A:B,2,FALSE)
where there are leading zeros in the text numbers [to 4 digits]
in the lookup col of the table_array (eg: 0010, 0100, 0002, etc)
(Adapt the "0000" to suit the format in the table_array)

If it's text being matched, try:
=VLOOKUP(TRIM(A2),Sheet2!A:B,2,FALSE)
if there could be extraneous "invisible" whitespaces within the lookup value
in A2

Or, if its the other way around, try trimming col A in Sheet2 (the lookup
col in the table_array). In Sheet2, just place in say C1: =TRIM(A1), copy
down. Then cut-out col C and paste special as values to overwrite col A

Note that you might need to change the commas within the formulas above to
semicolons to suit your continental version of Excel

---

 

[Guest]

THANK YOU. THAT WAS MY PROBLEM. I HAD SOME ENTERED AS TEXT & SOME AS
NUMBERS. I HAVE COVERTED THEM ALL TO NUMBERS & USED THE "CELL +0 " SOLUTION.

--
heather guenther

I entered a vlookup function in one of my spreadsheets. I then copied it
down the column, but everyonce in a while the lookup didn't work & came up
with N/A. Yet all the items were in the data range. Why would it not be
consistent???

Probably because the data within the lookup col in the vlookup's table_array
and the lookup values may not be consistent. For example: If it's numbers
being matched, these need to be consistent: either match text numbers vs text
numbers, or real numbers vs real numbers (lookup values vs lookup col in
table _array)

So instead of say:
=VLOOKUP(A2,Sheet2!A:B,2,FALSE)

Try it as:
=VLOOKUP(A2+0,Sheet2!A:B,2,FALSE)
The "+0" is one way to coerce the text lookup number in A2 to a real number

Or, try:
=VLOOKUP(A2&"",Sheet2!A:B,2,FALSE)
to convert the real number in A2 to a text number
so that it will match the text numbers in the lookup col of the table_array

Or, try something like:
=VLOOKUP(TEXT(A2,"0000"),Sheet2!A:B,2,FALSE)
where there are leading zeros in the text numbers [to 4 digits]
in the lookup col of the table_array (eg: 0010, 0100, 0002, etc)
(Adapt the "0000" to suit the format in the table_array)

If it's text being matched, try:
=VLOOKUP(TRIM(A2),Sheet2!A:B,2,FALSE)
if there could be extraneous "invisible" whitespaces within the lookup value
in A2

Or, if its the other way around, try trimming col A in Sheet2 (the lookup
col in the table_array). In Sheet2, just place in say C1: =TRIM(A1), copy
down. Then cut-out col C and paste special as values to overwrite col A

Note that you might need to change the commas within the formulas above to
semicolons to suit your continental version of Excel

---

 

[Guest]

Thank you. That was my problem. I had some entered as text & some as
numbers. I have coverted them all to numbers & used the "cell +0 " solution

Glad that option helped! Thanks for feedback.
Hold the other options. These might come in handy elsewhere.

---