IF formula

srcurrie · Mar 27th, 2002, 07:36 AM

I am trying to create an IF formula to add to the following formula, that when A1 is blank this cell wont show up with N/A.

=VLOOKUP(A1,'Price Sheet'!$A$1:$C$300,2,FALSE)
This cell is in B1

Thanks

Ian Mac · Mar 27th, 2002, 07:39 AM

Quote:

On 2002-03-27 06:36, srcurrie wrote:
I am trying to create an IF formula to add to the following formula, that when A1 is blank this cell wont show up with N/A.

=VLOOKUP(A1,'Price Sheet'!$A$1:$C$300,2,FALSE)
This cell is in B1

Thanks

use: =IF(LEN(A1)>0,VLOOKUP(A1,'Price Sheet'!$A$1:$C$300,2,FALSE),"")

Aladin Akyurek · Mar 27th, 2002, 08:36 AM

Quote:

On 2002-03-27 06:36, srcurrie wrote:
I am trying to create an IF formula to add to the following formula, that when A1 is blank this cell wont show up with N/A.

=VLOOKUP(A1,'Price Sheet'!$A$1:$C$300,2,FALSE)
This cell is in B1

Thanks

Enlarging what Ian Mac has suggested:

=IF(AND(LEN(A1),COUNTIF('Price Sheet'!$A$1:$A$300,A1)),VLOOKUP(A1,'Price Sheet'!$A$1:$C$300,2,0),"")

The 0 in VLOOKUP is substituted for FALSE because FALSE=0 as far as Excel is concerned.

I'd suggest to change the sheet name Price Sheet in order to eliminate single quotes arount it either to

PriceSheet

or just

Prices.

srcurrie · Mar 27th, 2002, 09:10 AM

Thanks AGAIN.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 27th, 2002, 07:36 AM	#1
srcurrie New Member Join Date: Mar 2002 Posts: 18	I am trying to create an IF formula to add to the following formula, that when A1 is blank this cell wont show up with N/A. =VLOOKUP(A1,'Price Sheet'!$A$1:$C$300,2,FALSE) This cell is in B1 Thanks

Mar 27th, 2002, 09:10 AM	#4
srcurrie New Member Join Date: Mar 2002 Posts: 18	Thanks AGAIN.