Vlookup and when to stop

[Tunaman72]

I have created a list of part numbers, and am using a drop down screen to select part numbers (nearly 500). In the second and third column I am using vlookup so that I can get prices, which I want to total at the end. However, if I copy and paste the vlookup formula to a maximum of say 20 possible part numbers, if use only 5, there is an error message on the remaining 15 which means I can't get a total. Is there a way of ignoring the formula in cell B6 if, for example cell A6 is empty, but using it if A6 has something in it?
I am using Excel for mac 2008.

I would be very grateful for any assistance

 

[dave3009]

Hi Tunaman72

You might be able to evaulate the VLOOKUP and leave it blank or 0 if there is nothing in the A column

=IFERROR(VLOOKUP(A2, Range, 2, 0), "") '..Change the "" to 0 to return a blank.

I'm fairly sure IFERROR will work in MacOffice2008 but if not you can use

=IF(ISERROR(VLOOKUP(A2, Range, 2, 0)), "", VLOOKUP(A2, Range, 2, 0)) again with the ""

Regards

Dave

 

[lenze]

Hi and welcome to the board!!
You can adjust your formula to avoid the error. Something like

=IF(ISERROR(yourVLOOKUP),"",yourVLOOKUP)

Or maybe in B2, copied down

If (A2="","",yourVOOKUP)

where Column "A" has your dropdown and Column "B" has your formulas.
There might be other ways using code. If interested, post back with details/

lenze

 

[Tunaman72]

Dave,

The first one got it, many thanks

Tunaman

Hi Tunaman72

You might be able to evaulate the VLOOKUP and leave it blank or 0 if there is nothing in the A column

=IFERROR(VLOOKUP(A2, Range, 2, 0), "") '..Change the "" to 0 to return a blank.

I'm fairly sure IFERROR will work in MacOffice2008 but if not you can use

=IF(ISERROR(VLOOKUP(A2, Range, 2, 0)), "", VLOOKUP(A2, Range, 2, 0)) again with the ""

Regards

Dave

 

[Tunaman72]

Lenze,
Many thanks for your reply, didn't attempt, as Dave's did the trick, but thanks anyway.

Hi and welcome to the board!!
You can adjust your formula to avoid the error. Something like

=IF(ISERROR(yourVLOOKUP),"",yourVLOOKUP)

Or maybe in B2, copied down

If (A2="","",yourVOOKUP)

where Column "A" has your dropdown and Column "B" has your formulas.
There might be other ways using code. If interested, post back with details/

lenze