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