vlookup

vac · May 26th, 2002, 01:24 AM

I have a vlookup on one of my worksheets which gathers information from another.
How do I stop #N/A appearing when there is nothing in the other lookup cells.
=VLOOKUP(B8:B23,parts,2,FALSE)
is my formula.
Once this reutrns the results, which are prices of goods, then I have another formula to add the results of the cells
=SUM(C8:C23)
THIS SUM WILL NOT WORK IF THERE ARE ANY #N/A VALUES
Can anyone help

Nimrod · May 26th, 2002, 01:33 AM

=IF(ISERROR(VLOOKUP(B8:B23,parts,2,FALSE)),,VLOOKUP(B8:B23,parts,2,FALSE))

Aladin Akyurek · May 26th, 2002, 01:55 AM

Quote:

On 2002-05-26 00:24, vac wrote:
I have a vlookup on one of my worksheets which gathers information from another.
How do I stop #N/A appearing when there is nothing in the other lookup cells.
=VLOOKUP(B8:B23,parts,2,FALSE)
is my formula.
Once this reutrns the results, which are prices of goods, then I have another formula to add the results of the cells
=SUM(C8:C23)
THIS SUM WILL NOT WORK IF THERE ARE ANY #N/A VALUES
Can anyone help

Keep #N/A's and use

=SUMIF(C8:C23,"<>#N/A")

instead of =SUM(C8:C23) to add up the retrieved values.

If you insist on suppressing #N/A's, use the more efficient:

=IF(COUNTIF(the-first-column-range-of-the-lookup-table,lookup-value),VLOOKUP(lookup-value,lookup-table,table-column-to-look-in,0),0)

The 0 in the VLOOKUP bit means the same thing to Excel as FALSE. The last 0 indicates the value to be returned in case the lookup-value is not available in the lookup table.

The formula that you use

=VLOOKUP(B8:B23,parts,2,FALSE)

has an unusual lookup-value, that's, B8:B28. You can't lookup a range of values in "parts" (presumably, your lookup table) in one go with this formula.

tleonardo1 · Jun 10th, 2002, 03:09 PM

Aladin or anyone else.

"The formula that you use

=VLOOKUP(B8:B23,parts,2,FALSE)

has an unusual lookup-value, that's, B8:B28. You can't lookup a range of values in "parts" (presumably, your lookup table) in one go with this formula.
"

Is there anyway to look upa range of values and have it return values for each vlookup and them sum them?

Aladin Akyurek · Jun 10th, 2002, 03:28 PM

Is there anyway to look upa range of values and have it return values for each vlookup and them sum them?

Let =VLOOKUP(A1:A10,E1:F40,2) be your formula. Put this formula, say, in B1. While in B1, select B1:B10, hit control+shift+enter at the same time (not just enter). You'll get the result of 10 vlookups appear in B1:B10. You can then apply an appropriate computation to B1:B10. However, this doesn't have anything more to offer than a VLOOKUP formula like in B1 which is then copied down:

=VLOOKUP(A1,$B$1:$B$10,2)

The former, being an array formula, will be less desirable than the latter method because it will have some additional costs qua speed (time).

tleonardo1 · Jun 10th, 2002, 03:57 PM

So there is no way do have it all done in one cell?

Along the lines of:
=SUMIF(A3:A28,(VLOOKUP(A3,'Data Rel'!$A$1:$E$26,3)="TheValueIwant"),R3:R28)

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

May 26th, 2002, 01:24 AM	#1
vac Board Regular Join Date: May 2002 Posts: 211	I have a vlookup on one of my worksheets which gathers information from another. How do I stop #N/A appearing when there is nothing in the other lookup cells. =VLOOKUP(B8:B23,parts,2,FALSE) is my formula. Once this reutrns the results, which are prices of goods, then I have another formula to add the results of the cells =SUM(C8:C23) THIS SUM WILL NOT WORK IF THERE ARE ANY #N/A VALUES Can anyone help

May 26th, 2002, 01:33 AM	#2
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	=IF(ISERROR(VLOOKUP(B8:B23,parts,2,FALSE)),,VLOOKUP(B8:B23,parts,2,FALSE))

Jun 10th, 2002, 03:09 PM	#4
tleonardo1 New Member Join Date: Mar 2002 Posts: 48	Aladin or anyone else. "The formula that you use =VLOOKUP(B8:B23,parts,2,FALSE) has an unusual lookup-value, that's, B8:B28. You can't lookup a range of values in "parts" (presumably, your lookup table) in one go with this formula. " Is there anyway to look upa range of values and have it return values for each vlookup and them sum them?

Jun 10th, 2002, 03:28 PM	#5
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,314	Is there anyway to look upa range of values and have it return values for each vlookup and them sum them? Let =VLOOKUP(A1:A10,E1:F40,2) be your formula. Put this formula, say, in B1. While in B1, select B1:B10, hit control+shift+enter at the same time (not just enter). You'll get the result of 10 vlookups appear in B1:B10. You can then apply an appropriate computation to B1:B10. However, this doesn't have anything more to offer than a VLOOKUP formula like in B1 which is then copied down: =VLOOKUP(A1,$B$1:$B$10,2) The former, being an array formula, will be less desirable than the latter method because it will have some additional costs qua speed (time).

Jun 10th, 2002, 03:57 PM	#6
tleonardo1 New Member Join Date: Mar 2002 Posts: 48	So there is no way do have it all done in one cell? Along the lines of: =SUMIF(A3:A28,(VLOOKUP(A3,'Data Rel'!$A$1:$E$26,3)="TheValueIwant"),R3:R28)