ExcelBanter - Vlookup if 0 then POA

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Vlookup if 0 then POA (https://www.excelbanter.com/excel-worksheet-functions/128262-vlookup-if-0-then-poa.html)

Vlookup if 0 then POA

How do I set the result field to POA if Zero is returned when I use Vlookup?
I am working on a pricebook. When there is no price I would like to insert
POA (or even just a blank) I need to retain the vlookup formula.
In cell B1 enter =VLOOKUP(A1,Sheet1!A:B,2,FALSE)
In cell C1 I enter =IF(B:B=0,"POA",B:B)
How can I combine these 2 formulas so the final result is in B1 and there is
no need for C1.

Thank you

Vlookup if 0 then POA

Thank you so much. I had been trying different combinations for about 45
minutes. Your response was within seconds of my post. Very much
appreciated.

"galimi" wrote:

Obwan,

You should be able to use an IF statement to test your VLOOKUP

=If(VLOOKUP(A1,Sheet1!A:B,2,FALSE)=0,"POA",VLOOKUP (A1,Sheet1!A:B,2,FALSE))

--
http://HelpExcel.com

"Obwan" wrote:

How do I set the result field to POA if Zero is returned when I use Vlookup?
I am working on a pricebook. When there is no price I would like to insert
POA (or even just a blank) I need to retain the vlookup formula.
In cell B1 enter =VLOOKUP(A1,Sheet1!A:B,2,FALSE)
In cell C1 I enter =IF(B:B=0,"POA",B:B)
How can I combine these 2 formulas so the final result is in B1 and there is
no need for C1.

Thank you

Vlookup if 0 then POA

Hi

Depending upon how many such formulae you have, it would be quicker to
use
=IF(COUNTIF(Sheet1!A:A,A1),VLOOKUP(A1,Sheet1!A:B,2 ,FALSE),"POA")
as this would not require a double lookup.

--
Regards

Roger Govier

"Obwan" wrote in message
...
Thank you so much. I had been trying different combinations for
about 45
minutes. Your response was within seconds of my post. Very much
appreciated.

"galimi" wrote:

Obwan,

You should be able to use an IF statement to test your VLOOKUP

=If(VLOOKUP(A1,Sheet1!A:B,2,FALSE)=0,"POA",VLOOKUP (A1,Sheet1!A:B,2,FALSE))

--
http://HelpExcel.com

"Obwan" wrote:

How do I set the result field to POA if Zero is returned when I use
Vlookup?
I am working on a pricebook. When there is no price I would like
to insert
POA (or even just a blank) I need to retain the vlookup formula.
In cell B1 enter =VLOOKUP(A1,Sheet1!A:B,2,FALSE)
In cell C1 I enter =IF(B:B=0,"POA",B:B)
How can I combine these 2 formulas so the final result is in B1 and
there is
no need for C1.

Thank you

Vlookup if 0 then POA

Thanks Roger
No doubt I did it incorrectly but when I used this I still have zero and not
POA. Sorry I can't attach my spreadsheet so you can double check.

"Roger Govier" wrote:

Hi

Depending upon how many such formulae you have, it would be quicker to
use
=IF(COUNTIF(Sheet1!A:A,A1),VLOOKUP(A1,Sheet1!A:B,2 ,FALSE),"POA")
as this would not require a double lookup.

--
Regards

Roger Govier

"Obwan" wrote in message
...
Thank you so much. I had been trying different combinations for
about 45
minutes. Your response was within seconds of my post. Very much
appreciated.

"galimi" wrote:

Obwan,

You should be able to use an IF statement to test your VLOOKUP

=If(VLOOKUP(A1,Sheet1!A:B,2,FALSE)=0,"POA",VLOOKUP (A1,Sheet1!A:B,2,FALSE))

--
http://HelpExcel.com

"Obwan" wrote:

How do I set the result field to POA if Zero is returned when I use
Vlookup?
I am working on a pricebook. When there is no price I would like
to insert
POA (or even just a blank) I need to retain the vlookup formula.
In cell B1 enter =VLOOKUP(A1,Sheet1!A:B,2,FALSE)
In cell C1 I enter =IF(B:B=0,"POA",B:B)
How can I combine these 2 formulas so the final result is in B1 and
there is
no need for C1.

Thank you

Vlookup if 0 then POA

No, my mistake.
I totally misread the question and thought it was if the Item didn't
exist in A:A you needed POA, not that the lookup value of the item was
zero.
Sorry for the confusion.

--
Regards

Roger Govier

"Obwan" wrote in message
...
Thanks Roger
No doubt I did it incorrectly but when I used this I still have zero
and not
POA. Sorry I can't attach my spreadsheet so you can double check.

"Roger Govier" wrote:

Hi

Depending upon how many such formulae you have, it would be quicker
to
use
=IF(COUNTIF(Sheet1!A:A,A1),VLOOKUP(A1,Sheet1!A:B,2 ,FALSE),"POA")
as this would not require a double lookup.

--
Regards

Roger Govier

"Obwan" wrote in message
...
Thank you so much. I had been trying different combinations for
about 45
minutes. Your response was within seconds of my post. Very much
appreciated.

"galimi" wrote:

Obwan,

You should be able to use an IF statement to test your VLOOKUP

=If(VLOOKUP(A1,Sheet1!A:B,2,FALSE)=0,"POA",VLOOKUP (A1,Sheet1!A:B,2,FALSE))

--
http://HelpExcel.com

"Obwan" wrote:

How do I set the result field to POA if Zero is returned when I
use
Vlookup?
I am working on a pricebook. When there is no price I would
like
to insert
POA (or even just a blank) I need to retain the vlookup formula.
In cell B1 enter =VLOOKUP(A1,Sheet1!A:B,2,FALSE)
In cell C1 I enter =IF(B:B=0,"POA",B:B)
How can I combine these 2 formulas so the final result is in B1
and
there is
no need for C1.

Thank you