Home

Using Excel lookup based on multiple criteria

Highlighted
Occasional Contributor

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:CGIPS
Program 11453 21176
Program 2146393143119
Program 321521132168

 

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:

 

ProgramTypeCount
Program 1C14
Program 1G53
Program 1P21
Program 1S176
Program 2C146
Program 2G393
Program 2I14
Program 2P3
Program 2S119
Program 3C21
Program 3G521
Program 3I13
Program 3P2
Program 3S168

 

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
Highlighted

Hi Bill,

 

In terms of rows in columns for such sample

image.png

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

 

Highlighted

Bill,

 

a pivot table is the way to go.

Program in row area.

Type in column area.

Count in value area.

 

Highlighted

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.

 

2018-05-08_09-14-11.png

 

 

 

Highlighted

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.

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

Bill,

 

Do I understand correctly the combination of the program # and type is not repeated in your list?

Highlighted

I'd go with the pivot table approach suggested above. If you add more data to the source table, you can refresh the pivot table. 

Highlighted

IMHO, both works. Attached are variants with formula and Pivot Table

Highlighted
Type "G" might be used by 14 out of 16 Programs, but type "I" might be used by 2. No program uses a type more than once.
Highlighted

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.