vlookup using 2 reference columns

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
 
S

Steve S.

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

Pat

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


---


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

Aladin Akyurek

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Vlookup-Doesn't return values under 1 2
If/Vlookup override 4
Multiple Columns in Sum Range using SUMIFS 1
lookup help. lookup result based on data in 2 columns 3
VLookup sum multiple matches 1
Vlookup doesn't work: results are wrong 2
Copy columns, but not hidden ones 3
how do I count rows with text values in several columns. 4

Top