vlookup using 2 reference columns

  • Thread starter Thread starter DanS
  • Start date Start date
D

DanS

I have 2 columns of information that I want to use in a vlookup.

The first column is a number that indicates what a customer does.
The second column has the customers size (S,M,L).

eg

Industry Size Total Sales
1411 Small $$$
1411 Medium $$$
1411 Large $$$
1516 Small $$$
1516 Medium $$$
1602 Small $$$
1711 Small $$$

My vlookup wants to return the Total Sales figure.
For example, I want to know how much I sold to Small customers in th
1411 industry.

Any ideas
 
Someone else may come up with a cleaner function - but
here's a workaround I used for something similar.

Create a helper column that combines Industry and size.
=concatenate(Industry,Size)
Then place this where you want the result of total sales
for what industry/size.
=SUMIF(helper cell,lookup value,total sales)
For your lookup value, you can have two cells that
concatenate, or you can enter it manually.

Hope it helps...curious what others will come up with.
 
-----Original Message-----
I have 2 columns of information that I want to use in a vlookup.

The first column is a number that indicates what a customer does.
The second column has the customers size (S,M,L).

eg

Industry Size Total Sales
1411 Small $$$
1411 Medium $$$
1411 Large $$$
1516 Small $$$
1516 Medium $$$
1602 Small $$$
1711 Small $$$

My vlookup wants to return the Total Sales figure.
For example, I want to know how much I sold to Small customers in the
1411 industry.

Any ideas?


---
Message posted

.
YOu can also add a column to concatenate [=CONCATENATE
Click to expand...
(C1,C2)]the first two columns and then do your lookup
against the concatenated column and use the concatenated
value as the reference.
 
Not a bad idea at all...

Steve S. said:
Someone else may come up with a cleaner function - but
here's a workaround I used for something similar.

Create a helper column that combines Industry and size.
=concatenate(Industry,Size)
Then place this where you want the result of total sales
for what industry/size.
=SUMIF(helper cell,lookup value,total sales)
For your lookup value, you can have two cells that
concatenate, or you can enter it manually.

Hope it helps...curious what others will come up with.
Click to expand...
 
Back
Top