Stringing together vlookups

Brendan

Board Regular
Joined
Aug 31, 2006
Messages
51
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?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
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

Board Regular
Joined
Aug 31, 2006
Messages
51
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

Well-known Member
Joined
May 14, 2003
Messages
3,829
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

Board Regular
Joined
Aug 31, 2006
Messages
51

ADVERTISEMENT

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

Board Regular
Joined
Aug 31, 2006
Messages
51
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

Well-known Member
Joined
Feb 25, 2002
Messages
4,053

ADVERTISEMENT

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
ABCDEFG
1Cow10Sheep5
2SheepHorse20Cow10
3SheepCow15Horse15
40
5SheepCowHorse30
Sheet2
 

Brendan

Board Regular
Joined
Aug 31, 2006
Messages
51
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 ..
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
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
ABCDEFG
1Cow10Sheep5
2SheepHorse20Cow10
3SheepCow15Horse15
4000
5SheepCowHorse30
6SheepCow15
Sheet2
 

Forum statistics

Threads
1,136,348
Messages
5,675,241
Members
419,555
Latest member
Paddington

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top