Multiple Vlookups In One CellAug 16, 2009
I have two lists of IDs in 2 columns. I want to sort of do a vlookup of each row in one column to see if it is in the next column, and I want to sum a third column that goes along with column 2 based on whether the same IDs from column one is in the second column. And I want to do all this in one cell. For example:
A B C
TestA TestZ 10
TestB TestY 20
TestC TestB 8
TestD TestA 7
I need one cell to calculate for me that based on the IDs in column A, column B matches with the IDs TestB and TestA, therefore giving me the sum of 15 (8+7). Doing a vlookup and auto-filling down and then summing up is not an option, so I'm trying to calculate it within one cell if possible. And I can't get sumproduct to work because the matching IDs are not necessarily in the same rows.