Forum Discussion
Using Excel lookup based on multiple criteria
Hi.
I am attempting to create a table using the VLOOKUP function that compares a name in the left column and a category across the top row and returns the value in a separate table. Here's an example:
| TYPE: | C | G | I | P | S |
| Program 1 | 14 | 53 | 21 | 176 | |
| Program 2 | 146 | 393 | 14 | 3 | 119 |
| Program 3 | 21 | 521 | 13 | 2 | 168 |
The formula that returns the numbers above should look at the left column and the top row and find where they are true in the following table:
| Program | Type | Count |
| Program 1 | C | 14 |
| Program 1 | G | 53 |
| Program 1 | P | 21 |
| Program 1 | S | 176 |
| Program 2 | C | 146 |
| Program 2 | G | 393 |
| Program 2 | I | 14 |
| Program 2 | P | 3 |
| Program 2 | S | 119 |
| Program 3 | C | 21 |
| Program 3 | G | 521 |
| Program 3 | I | 13 |
| Program 3 | P | 2 |
| Program 3 | S | 168 |
Suffice to say my actual reference table has more programs and types than listed here. I would also like to turn it into a template that I could reuse, going forward. I'm familiar with the VLOOKUP at a basic level, so I'm honestly not sure if/how it can find what I'm looking for. Any insight would be greatly appreciated.
10 Replies
All,
Thank you for your help. I was looking to work with formulas as I am more familiar with some of them. I had no previous encounters with pivot tables, so didn't know what to do with that answer.
I have since highlighted my original table and used Excel's search to look up pivot tables. It created what I was looking for. I don't quite understand how it works, but will teach myself how, now that I know it's easy to figure out.
Hello,
as I interpret your question, the table is already there and you want to enter the program and type and let a formula look up the value. Is that right? This can be done with an Index/Match instead of a Vlookup.
Take a look at the screenshot. The formula in cell J2 is
=INDEX($B$2:$F$4,MATCH(H2,$A$2:$A$4,0),MATCH(I2,$B$1:$F$1,0))
Copied down. If you add more rows to the lookup table, you need to adjust the formula accordingly.
- Ingeborg,
This is getting closer, but I need to go the other way. The data is coming from the right side table and needs to populate the left table. As in my response to Sergei, I have 16 actual programs taking 70 rows of data. There are 9 different "Types" and not every program uses every type. Hope this is clearer. I haven't tried to explain this type of information to anyone before.IMHO, both works. Attached are variants with formula and Pivot Table
Bill,
a pivot table is the way to go.
Program in row area.
Type in column area.
Count in value area.
Hi Bill,
In terms of rows in columns for such sample
in F2 you may use formula
=IFERROR(INDEX($A$1:$C$7,MATCH(1,INDEX(($E2=$A$1:$A$7)*($B$1:$B$7=F$1),0,1),0),3),"")
and drag it down and to the right
Sergei,
What you are showing only has one type of "P#", in no particular order, for the source table. I am having difficulty understanding how the formula relates so it is looking at the right information on my end.
My actual source table contains 16 different programs appearing from 1 to 6 times each, sorted alphabetically, depending on the number of types they have associated with them. There are 70 rows for those 16 programs and 9 different "types". Not every program has all the types associated with it.
I apologize that I need to be vague, as my data is sensitive. If you need more clarification, I would be happy to do so.
Bill,
Do I understand correctly the combination of the program # and type is not repeated in your list?
