- last edited on
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:
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:
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.
05-07-2018 01:28 PM
In terms of rows in columns for such sample
in F2 you may use formula
and drag it down and to the right
05-07-2018 01:29 PM
a pivot table is the way to go.
Program in row area.
Type in column area.
Count in value area.
05-07-2018 02:15 PM
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
Copied down. If you add more rows to the lookup table, you need to adjust the formula accordingly.
05-07-2018 02:39 PM
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.
05-07-2018 02:46 PM
05-07-2018 02:46 PM
Do I understand correctly the combination of the program # and type is not repeated in your list?
05-07-2018 02:48 PM
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.
05-07-2018 03:33 PM
05-07-2018 03:42 PM
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.
by Chris_Hafstein on June 02, 2020
by C_J_M on June 02, 2020
by Lisa LaCombe on June 02, 2020
by cuong on May 27, 2020
by Ingeborg Hawighorst on May 13, 2020