Forum Discussion

Bill Hebner's avatar
Bill Hebner
Copper Contributor
May 07, 2018

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 exa...
Formulas & Functions
Need Help
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 07, 2018

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

 

Bill Hebner's avatar
Bill Hebner
Copper Contributor
May 07, 2018

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.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 07, 2018

    Bill,

     

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

    • Bill Hebner's avatar
      Bill Hebner
      Copper Contributor
      May 07, 2018
      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.

Resources