vlookup questions

[jgomez]

Hello I noticed that everytime I use the vlookup function I get zeros on some of the cells. It works well, i just have that minor problem. I don't mind finding all and replacing but just wondering.

Also when would it be good to use the range_lookup??? I don't know what that is and I don't get excels explanation of it... oh and on the col_index_num... I put a 2 because that's what i have in the samples to practice on but would any other number go there??? or is that always a 2???

Thanks,

[jgomez]

oh and sometimes I get #n/a

[Blake 7]

Hello I noticed that everytime I use the vlookup function I get zeros on some of the cells. It works well, i just have that minor problem. I don't mind finding all and replacing but just wondering.

close your forula with:

)&"")

this will get rid of zeros

EDIT - to aid example

Example

=IF(VLOOKUP(C7,Sheet2!A8:L33,4,FALSE)=0,"",VLOOKUP(C7,Sheet2!A8:L33,4,FALSE))

This vlookup is running twice, once for zeros, then again, which is inefficient and a ball ache to write.

This however, is much shorter and does the same job

=IF(C7="","",VLOOKUP(C7,Sheet2!A8:L33,7,FALSE)&"")

Of course:

Another option would be to put ="" into the cells in "Sheet2!" which would also stop the zero (not forgetting to format cell to general)

Also when would it be good to use the range_lookup??? I don't know what that is and I don't get excels explanation of it... oh and on the col_index_num... I put a 2 because that's what i have in the samples to practice on but would any other number go there??? or is that always a 2???

please post your spreadsheet and i'll demonstrate

[arthurbr]

Hi, maybe first take a look at Xl's help about the VLOOKUP function ( activated with F1) and come back for fine tuning your questions?

[excelhariharan]

If the source value is zero, then VLOOKUP returns 0. Likewise, if the source is blank, then VLOOKUP still returns the value 0. For some purposes, this may not do—you need to know whether the cell being looked up is blank or if it really contains a 0.
Column index is decided by the column number of the field from which you need the information, for example, if you have table emp id, emp name, emp add. If you are using emp id to take the emp address details from the table. If you look at the table, it is present in the 3rd number column so the col index will be 3. So it is always decided by the position of the field name in the table from which you need the information.
It will return #N/A when the value is not found in the table.

[NBVC]

Some more VLOOKUP tutoring:

http://www.contextures.com/xlfunctions02.html

http://www.techonthenet.com/excel/formulas/vlookup.php

[Cutter]

Blake7

You'd only want to end the VLOOKUP() with &"" if you're looking up text values because it will convert numeric values to text.


One more source for learning VLOOKUP()
http://www.excelfunctions.net/ExcelVlookup.html

[royUK]

To hide zeroes you can use Tools -> Options -> View & uncheck Zeroes

[Blake 7]

Blake7

You'd only want to end the VLOOKUP() with &"" if you're looking up text values because it will convert numeric values to text.

Thanks Cutter for that, i wasn't aware.

@op - you can still use this old chesnut


=IF(VLOOKUP(C7,Sheet2!A8:L33,4,FALSE)=0,"",VLOOKUP(C7,Sheet2!A8:L33,4,FALSE))