Hello
I need help. The formula listed below works for all the employee numbers on
my spreadsheet which are in column B except for the employee number 63023. I
tried to convert this number to a text, general, number, reentered it on
another row and spreadsheet and it does not work; instead I get a zero value.
The only time it works is when I key it in as €˜63023. Why does it work for
all other 4 or 5 digit numbers and not 63023?


=IF(ISNA(VLOOKUP(B260,Sheet1!$B$5:$H$290,COLUMNS(S heet1!$B:$H),FALSE))=TRUE,0,VLOOKUP(B260,Sheet1!$B $5:$H$290,COLUMNS(Sheet1!$B:$H),FALSE))

Maybe the number 63023 has been entered as text in column B on Sheet1.

Tom

I'd still say it was a mismatched text/number thing.

Make sure you format both cells (the lookup value and the cell in the table that
matches!) and then reenter the value in both locations -- it's not enough to
just reformat the cell.

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble

Nain wrote:

Hello
I need help. The formula listed below works for all the employee numbers on
my spreadsheet which are in column B except for the employee number 63023. I
tried to convert this number to a text, general, number, reentered it on
another row and spreadsheet and it does not work; instead I get a zero value.
The only time it works is when I key it in as €˜63023. Why does it work for
all other 4 or 5 digit numbers and not 63023?

=IF(ISNA(VLOOKUP(B260,Sheet1!$B$5:$H$290,COLUMNS(S heet1!$B:$H),FALSE))=TRUE,0,VLOOKUP(B260,Sheet1!$B $5:$H$290,COLUMNS(Sheet1!$B:$H),FALSE))

--

Dave Peterson

Hi
I formatted the cells to match and it still does not work. I used a new
spreadsheet and keyed the data in and it still does not give me the correct
amount. When I key in another employee number in the the same cell - it will
retrieve the data so it is not the format in question . It just does not like
63023? Any other suggestions?

"Dave Peterson" wrote:

I'd still say it was a mismatched text/number thing.

Make sure you format both cells (the lookup value and the cell in the table that
matches!) and then reenter the value in both locations -- it's not enough to
just reformat the cell.

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble

Nain wrote:

Hello
I need help. The formula listed below works for all the employee numbers on
my spreadsheet which are in column B except for the employee number 63023. I
tried to convert this number to a text, general, number, reentered it on
another row and spreadsheet and it does not work; instead I get a zero value.
The only time it works is when I key it in as €˜63023. Why does it work for
all other 4 or 5 digit numbers and not 63023?

=IF(ISNA(VLOOKUP(B260,Sheet1!$B$5:$H$290,COLUMNS(S heet1!$B:$H),FALSE))=TRUE,0,VLOOKUP(B260,Sheet1!$B $5:$H$290,COLUMNS(Sheet1!$B:$H),FALSE))

--

Dave Peterson
.

It's not enough to just change the format. You have to change the value, too.

Remember to change the value in both locations, too.

And verify that your =vlookup() table is ok.

If you have the matching value in A23 and your =vlookup() looks like:

=vlookup(x99,sheet2!a1:b20,2,false)

It'll never find that match.

If this doesn't help, you may want to share the formula, too.

Nain wrote:

Hi
I formatted the cells to match and it still does not work. I used a new
spreadsheet and keyed the data in and it still does not give me the correct
amount. When I key in another employee number in the the same cell - it will
retrieve the data so it is not the format in question . It just does not like
63023? Any other suggestions?

"Dave Peterson" wrote:

I'd still say it was a mismatched text/number thing.

Make sure you format both cells (the lookup value and the cell in the table that
matches!) and then reenter the value in both locations -- it's not enough to
just reformat the cell.

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble

Nain wrote:

Hello
I need help. The formula listed below works for all the employee numbers on
my spreadsheet which are in column B except for the employee number 63023. I
tried to convert this number to a text, general, number, reentered it on
another row and spreadsheet and it does not work; instead I get a zero value.
The only time it works is when I key it in as €˜63023. Why does it work for
all other 4 or 5 digit numbers and not 63023?

=IF(ISNA(VLOOKUP(B260,Sheet1!$B$5:$H$290,COLUMNS(S heet1!$B:$H),FALSE))=TRUE,0,VLOOKUP(B260,Sheet1!$B $5:$H$290,COLUMNS(Sheet1!$B:$H),FALSE))

--

Dave Peterson
.


--

Dave Peterson