# Stringing together vlookups

#### Brendan

##### Board Regular
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
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
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
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

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
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

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
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

##### Well-known Member
Deleted by Ralph. Barry has a good solution, below.

#### Barry Katcher

##### Well-known Member
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

Replies
6
Views
140
Replies
5
Views
149
Replies
1
Views
36
Replies
4
Views
98
Replies
3
Views
114

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

### 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.

### Which adblocker are you using?

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

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