| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup if 0 then POA
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLookup a Vlookup | Excel Worksheet Functions | |||
| VLOOKUP Problem | Excel Discussion (Misc queries) | |||
| VLOOKUP Limitations | Excel Worksheet Functions | |||
| Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
| vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Linear Mode
