May 07 2018
12:40 PM
- last edited on
Jul 31 2018
08:04 AM
by
TechCommunityAP
May 07 2018
12:40 PM
- last edited on
Jul 31 2018
08:04 AM
by
TechCommunityAP
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.
May 07 2018 01:28 PM
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
May 07 2018 01:29 PM
Bill,
a pivot table is the way to go.
Program in row area.
Type in column area.
Count in value area.
May 07 2018 02:15 PM
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.
May 07 2018 02:39 PM
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.
May 07 2018 02:46 PM
May 07 2018 02:46 PM
Bill,
Do I understand correctly the combination of the program # and type is not repeated in your list?
May 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.
May 07 2018 02:57 PM
IMHO, both works. Attached are variants with formula and Pivot Table
May 07 2018 03:33 PM
May 07 2018 03:42 PM
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.