Excel question

[polarmystery]

Can someone tell me why this happens when I do a vlookup? Column E shows the formula I used and column F shows what column E returned after you hit enter. In reality there is no column F, just used to show the answer.

-------A-------B-------C-------------D--------------E---------------------F

1----1K3------1------1K3------=vlookup(D1,A$1:B$4,2)------------1

2----100------2------2FG------=vlookup(D2,A$1:B$4,2)------------3

3----2FG------3------100------=vlookup(D3,A$1:B$4,2)------------#N/A

4----3Q5------4------3Q5------=vlookup(D4,A$1:B$4,2)------------4


The #N/A should return a 2 but it doesn't.



EDIT: For clarity and spacing

 

[Jadow]

I always put comma false at the end of vlookup also name your range.

=vlookup(a2,RANGE,2,false)

 

[Jadow]

also the "2" in your vlookup formula isn't saying "return a 2" it says "return the data that's in column 2 of the range"

 

[darkxshade]

Originally posted by: Jadow
I always put comma false at the end of vlookup also name your range.

=vlookup(a2,RANGE,2,false)

This makes all the difference

 

[polarmystery]

Originally posted by: Jadow
also the "2" in your vlookup formula isn't saying "return a 2" it says "return the data that's in column 2 of the range"

correct it's supposed to return the value that is in the second row of the array but for some reason if the cell value in the vlookup is numeric, it returns N/A if it is alphanumeric it's fine.

 

[polarmystery]

Originally posted by: darkxshade

Originally posted by: Jadow
I always put comma false at the end of vlookup also name your range.

=vlookup(a2,RANGE,2,false)

This makes all the difference

that didn't help

 

[AreaCode707]

Yeah, =vlookup(D3,A$1:B$4,2,0) or =vlookup(D3,A$1:B$4,2,false)

Also, vlookups are finicky about text-to-columns. It's possible that one of your source cells is reading as text instead of as a column so the data doesn't match. Kind of unlikely, since usually the entire column would be messed up, but possible.

 

[AreaCode707]

Originally posted by: polarmystery

Originally posted by: Jadow
also the "2" in your vlookup formula isn't saying "return a 2" it says "return the data that's in column 2 of the range"

correct it's supposed to return the value that is in the second row of the array but for some reason if the cell value in the vlookup is numeric, it returns N/A if it is alphanumeric it's fine.

Yep, text to columns!! Excel is auto-assigning the cell type based on the cell contents. The alpha in some is causing Excel to assign the cell formatting type of "text" or "general" but the numeric alone are probably being formatted as "numeric".

Highlight the column, right click and format cells as something standard. If that doesn't work alone, go to Data > Text to Columns.

 

[polarmystery]

Originally posted by: AreaCode707

Originally posted by: polarmystery

Originally posted by: Jadow
also the "2" in your vlookup formula isn't saying "return a 2" it says "return the data that's in column 2 of the range"

correct it's supposed to return the value that is in the second row of the array but for some reason if the cell value in the vlookup is numeric, it returns N/A if it is alphanumeric it's fine.

Yep, text to columns!! Excel is auto-assigning the cell type based on the cell contents. The alpha in some is causing Excel to assign the cell formatting type of "text" or "general" but the numeric alone are probably being formatted as "numeric".

Highlight the column, right click and format cells as something standard. If that doesn't work alone, go to Data > Text to Columns.

I highlighted the column and changed to a standard like you said and I got nothing different. When I did text>columns from Data it got worse :-(

This sucks

 

[AreaCode707]

Originally posted by: polarmystery

Originally posted by: AreaCode707

Originally posted by: polarmystery

Originally posted by: Jadow
also the "2" in your vlookup formula isn't saying "return a 2" it says "return the data that's in column 2 of the range"

correct it's supposed to return the value that is in the second row of the array but for some reason if the cell value in the vlookup is numeric, it returns N/A if it is alphanumeric it's fine.

Yep, text to columns!! Excel is auto-assigning the cell type based on the cell contents. The alpha in some is causing Excel to assign the cell formatting type of "text" or "general" but the numeric alone are probably being formatted as "numeric".

Highlight the column, right click and format cells as something standard. If that doesn't work alone, go to Data > Text to Columns.

I highlighted the column and changed to a standard like you said and I got nothing different. When I did text>columns from Data it got worse :-(

This sucks

Got worse; stuff that was pulling now isn't?

Run the vlookup, sort by NA
Highlight the source data for the NA results only
Text to columns

 

[Jadow]

yeah that's it, highlight the matching column in both places, data/text to columns, next, choose text format, next finish (I dont' remeber the exact sequence)

excel always does a lousy job wiht this, It's always good to change both columns to text before using vlookup.

also look out for phantom spaces and leading zeroes.

 

[polarmystery]

Originally posted by: AreaCode707

Originally posted by: polarmystery

Originally posted by: AreaCode707

Originally posted by: polarmystery

Originally posted by: Jadow
also the "2" in your vlookup formula isn't saying "return a 2" it says "return the data that's in column 2 of the range"

correct it's supposed to return the value that is in the second row of the array but for some reason if the cell value in the vlookup is numeric, it returns N/A if it is alphanumeric it's fine.

Yep, text to columns!! Excel is auto-assigning the cell type based on the cell contents. The alpha in some is causing Excel to assign the cell formatting type of "text" or "general" but the numeric alone are probably being formatted as "numeric".

Highlight the column, right click and format cells as something standard. If that doesn't work alone, go to Data > Text to Columns.

I highlighted the column and changed to a standard like you said and I got nothing different. When I did text>columns from Data it got worse :-(

This sucks

Got worse; stuff that was pulling now isn't?

Run the vlookup, sort by NA
Highlight the source data for the NA results only
Text to columns

Great! That worked. Now the N/A's that show are values that aren't in the array list which is fine. Thanks again.

 

[AreaCode707]

Originally posted by: polarmystery

Originally posted by: AreaCode707

Originally posted by: polarmystery

Originally posted by: AreaCode707

Originally posted by: polarmystery

Originally posted by: Jadow
also the "2" in your vlookup formula isn't saying "return a 2" it says "return the data that's in column 2 of the range"

correct it's supposed to return the value that is in the second row of the array but for some reason if the cell value in the vlookup is numeric, it returns N/A if it is alphanumeric it's fine.

Yep, text to columns!! Excel is auto-assigning the cell type based on the cell contents. The alpha in some is causing Excel to assign the cell formatting type of "text" or "general" but the numeric alone are probably being formatted as "numeric".

Highlight the column, right click and format cells as something standard. If that doesn't work alone, go to Data > Text to Columns.

I highlighted the column and changed to a standard like you said and I got nothing different. When I did text>columns from Data it got worse :-(

This sucks

Got worse; stuff that was pulling now isn't?

Run the vlookup, sort by NA
Highlight the source data for the NA results only
Text to columns

Great! That worked. Now the N/A's that show are values that aren't in the array list which is fine. Thanks again.

NP. I <3 Excel.