ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Vlookup problem (https://www.excelbanter.com/excel-worksheet-functions/245301-vlookup-problem.html)

Debbie[_4_]

Vlookup problem
 
Hi!

The problem I am having is in the lookup table, sometimes row 5 is
empty. When it is, it puts a zero in my cell. I want it to be blank.
Can I do this? I have pasted my formula.

=IF(ISERROR(VLOOKUP($A7,BB!$A$1:$S$5000,5,FALSE)), "",VLOOKUP($A7,BB!$A
$1:$S$5000,5,FALSE))

Debbie[_4_]

Vlookup problem
 
On Oct 12, 9:04*pm, Debbie wrote:
Hi!

The problem I am having is in the lookup table, sometimes row 5 is
empty. When it is, it puts a zero in my cell. I want it to be blank.
Can I do this? I have pasted my formula.

=IF(ISERROR(VLOOKUP($A7,BB!$A$1:$S$5000,5,FALSE)), "",VLOOKUP($A7,BB!$A
$1:$S$5000,5,FALSE))


Sorry, column 5 not row 5

Pete_UK

Vlookup problem
 
Well, it's a bit messy, but you could do this:

=IF(ISERROR(VLOOKUP($A7,BB!$A$1:$S$5000,5,0)),"",I F(VLOOKUP($A7,BB!$A
$1:$S$5000,5,0)="","",VLOOKUP($A7,BB!$A$1:$S$5000, 5,0)))

Hope this helps.

Pete

On Oct 13, 2:05*am, Debbie wrote:
On Oct 12, 9:04*pm, Debbie wrote:

Hi!


The problem I am having is in the lookup table, sometimes row 5 is
empty. When it is, it puts a zero in my cell. I want it to be blank.
Can I do this? I have pasted my formula.


=IF(ISERROR(VLOOKUP($A7,BB!$A$1:$S$5000,5,FALSE)), "",VLOOKUP($A7,BB!$A
$1:$S$5000,5,FALSE))


Sorry, column 5 not row 5



T. Valko

Vlookup problem
 
What is the data type of the returned value?

If it's TEXT try this:

=IF(ISERROR(VLOOKUP($A7,BB!$A$1:$S$5000,5,0)),"",T (VLOOKUP($A7,BB!$A
$1:$S$5000,5,0)))

If it's numeric will 0 be an otherwise valid result?

--
Biff
Microsoft Excel MVP


"Debbie" wrote in message
...
Hi!

The problem I am having is in the lookup table, sometimes row 5 is
empty. When it is, it puts a zero in my cell. I want it to be blank.
Can I do this? I have pasted my formula.

=IF(ISERROR(VLOOKUP($A7,BB!$A$1:$S$5000,5,FALSE)), "",VLOOKUP($A7,BB!$A
$1:$S$5000,5,FALSE))




Jacob Skaria

Vlookup problem
 
'if col 5 is text then you can try
=IF(ISERROR(VLOOKUP($A7,bb!$A$1:$S$5000,5,FALSE)), "",T(VLOOKUP($A7,bb!$A$1:$S$5000,5,FALSE)))

'or other wise
=IF(ISERROR(VLOOKUP($A7,bb!$A$1:$S$5000,5,FALSE)), "",IF(VLOOKUP($A7,bb!$A$1:$S$5000,5,FALSE)="","",V LOOKUP($A7,bb!$A$1:$S$5000,5,FALSE)))


If this post helps click Yes
---------------
Jacob Skaria


"Debbie" wrote:

Hi!

The problem I am having is in the lookup table, sometimes row 5 is
empty. When it is, it puts a zero in my cell. I want it to be blank.
Can I do this? I have pasted my formula.

=IF(ISERROR(VLOOKUP($A7,BB!$A$1:$S$5000,5,FALSE)), "",VLOOKUP($A7,BB!$A
$1:$S$5000,5,FALSE))


Debbie[_4_]

Vlookup problem
 
On Oct 12, 9:31*pm, Jacob Skaria
wrote:
'if col 5 is text then you can try
=IF(ISERROR(VLOOKUP($A7,bb!$A$1:$S$5000,5,FALSE)), "",T(VLOOKUP($A7,bb!$A$1:*$S$5000,5,FALSE)))

'or other wise
=IF(ISERROR(VLOOKUP($A7,bb!$A$1:$S$5000,5,FALSE)), "",IF(VLOOKUP($A7,bb!$A$1*:$S$5000,5,FALSE)="","", VLOOKUP($A7,bb!$A$1:$S$5000,5,FALSE)))

If this post helps click Yes
---------------
Jacob Skaria



"Debbie" wrote:
Hi!


The problem I am having is in the lookup table, sometimes row 5 is
empty. When it is, it puts a zero in my cell. I want it to be blank.
Can I do this? I have pasted my formula.


=IF(ISERROR(VLOOKUP($A7,BB!$A$1:$S$5000,5,FALSE)), "",VLOOKUP($A7,BB!$A
$1:$S$5000,5,FALSE))- Hide quoted text -


- Show quoted text -


Thank you. The second works great.


All times are GMT +1. The time now is 11:57 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com