• 667K Members
• 4,566 Online
• 821K Conversations

## Using Excel lookup based on multiple criteria

Highlighted
Occasional Contributor

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

# Re: Using Excel lookup based on multiple criteria

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

Highlighted

# Re: Using Excel lookup based on multiple criteria

Bill,

a pivot table is the way to go.

Program in row area.

Type in column area.

Count in value area.

Highlighted

# Re: Using Excel lookup based on multiple criteria

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.

Highlighted

# Re: Using Excel lookup based on multiple criteria

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

# Re: Using Excel lookup based on multiple criteria

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

# Re: Using Excel lookup based on multiple criteria

Bill,

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

Highlighted

# Re: Using Excel lookup based on multiple criteria

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

# Re: Using Excel lookup based on multiple criteria

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

Highlighted

# Re: Using Excel lookup based on multiple criteria

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

# Re: Using Excel lookup based on multiple criteria

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.