Stringing together vlookups

[Brendan]

Im not sure if Im using the right function here.

But I have 3 columns for products, at any given time, 0 to 3 of these columns may have data in them.
Im wanting relate each product to a price.

If I dont enter all 3 colums though it returns with a #N/A error.
If only one product is sold, I want it to return with just the one product price.

Any help?

 

[RalphA]

I don't understand what it is you need. Perhaps, if you posted an example, along with the result you expect, we might be able to help you.

 

[Brendan]

Okay, so for example

I have 3 columns.
and several products.

Sheep costs $5
cow costs $10
Horse costs $15

Column A might have 'Horse', and B+C blank and I want the result to return 15.
If I have
A | B | C
Cow | | Horse = $25 ..
ATM this would return #N/A because one of the columns is empty.

Does that make a bit more sense?
The columns may not always have information in them, but I still want them to be looked at incase they do have information in.

 

[RalphA]

Assuming that:
if you have Cow in any row, it will be in column A,
if you have Sheep in any row, it will be in column B,
if you have Horse in any row, it will be in column C. and,
your data starts in cell A3.

Assuming, also, that you want the answer on each row, one solution is:

In cell D3, enter:
=IF(A3="Cow",10,0)+IF(B3="Sheep",5,0)+IF(C3="Horse",15,0)

 

[Brendan]

Assuming that:
if you have Cow in any row, it will be in column A,
if you have Sheep in any row, it will be in column B,
if you have Horse in any row, it will be in column C. and,
your data starts in cell A3.

Assuming, also, that you want the answer on each row, one solution is:

In cell D3, enter:
=IF(A3="Cow",10,0)+IF(B3="Sheep",5,0)+IF(C3="Horse",15,0)

What happens if I can have Cow|cow|cow
or Sheep|Horse|Cow

This is why I was thinking Vlookup would be better, but instead of returning #N/A I want 'blank' to = 0

In another sheet I have
A | B
Cow | 5
Sheep | 5
Horse | 15

and using the function
=VLOOKUP($I3,Sheet3!$D$1:$E$12,2,FALSE)+VLOOKUP($J3,Sheet3!$D$1:$E$12,2,FALSE)+VLOOKUP($K3,Sheet3!$D$1:$E$12,2,FALSE)

 

[Brendan]

Well, thats the actual formula Im using in my sheet, instead of the sheep horse cow example I gave. But the problem is when one cell is blank I get #N/A.

Anyway to remove that error?

 

[Barry Katcher]

This is really very inelegant and clumsy, but try this.

Formula in D1 is: =IF(ISNA(VLOOKUP(A1,$F$1:$G$3,2,0)),0,VLOOKUP(A1,$F$1:$G$3,2,0))+IF(ISNA(VLOOKUP(B1,$F$1:$G$3,2,0)),0,VLOOKUP(B1,$F$1:$G$3,2,0))+IF(ISNA(VLOOKUP(C1,$F$1:$G$3,2,0)),0,VLOOKUP(C1,$F$1:$G$3,2,0))

Book1
	A	B	C	D	E	F	G
1	Cow			10		Sheep	5
2		Sheep	Horse	20		Cow	10
3	Sheep	Cow		15		Horse	15
4				0
5	Sheep	Cow	Horse	30
Sheet2

 

[Brendan]

Ouch that hurts my eyes.

Knew it would involve the ISNA function.. still havent come to grasp with that one there .. thought there may have been a simpler way ..

 

[RalphA]

Deleted by Ralph. Barry has a good solution, below.

 

[Barry Katcher]

This is a bit better. By including the 4th row (0 - 0) in the lookup list, you eliminate the need for the ISNA.

Formula in D1, copied down, is: =VLOOKUP(A1,$F$1:$G$4,2,0)+VLOOKUP(B1,$F$1:$G$4,2,0)+VLOOKUP(C1,$F$1:$G$4,2,0)

Book1
	A	B	C	D	E	F	G
1	Cow			10		Sheep	5
2		Sheep	Horse	20		Cow	10
3	Sheep	Cow		15		Horse	15
4				0		0	0
5	Sheep	Cow	Horse	30
6	Sheep	Cow		15
Sheet2