Nested index, vlookup and match

**Maddyv** · 02-02-2010, 01:31 PM

I was wondering if there's a way to nest index, vlookup and match? I'm trying to first establish the first criteria (vlookup) and if that exists, then match 2 other criterias to return the result from a table (index)? For example, the formula I'm working on is:

=INDEX(A1:D20, VLOOKUP(X,B1:B20,MATCH(Y,A1;A20,0),MATCH(Z,B1:D1,0)))

**NBVC** · 02-02-2010, 01:33 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**Maddyv** · 02-02-2010, 02:39 PM

Source of data spreadsheet contains:

Site Activity January February March
Apple Spinach 1
Banana Lettuce 5
Cherry Beans 7
Guava Beet 4

Results on output spreadsheet should be:

January February March
Apple Lettuce 0 0 0
Banana Beans 0 0 0
Cherry Beans 0 0 7
Guava Beet 0 4 0

so basically, what I'm trying to accomplish is: if the 'fruit' and 'vegetable' is a match, as well as the month, then return the value under that month that also matches. If not, then return '0'.

**NBVC** · 02-02-2010, 02:48 PM

Say your first table is in A1:E5 and your second table is in A9:E13

then in C10: =SUMPRODUCT(($A$2:$A$5=$A10)*($B$2:$B$5=$B10)*($C$1:$E$1=C$9)*($C$2:$E$5)) copied across the matrix...

**DonkeyOte** · 02-02-2010, 02:51 PM

If you happen to be using XL2007 (unclear) you might also wish to consider use of SUMIFS

C10: =SUMIFS(C$2:C$5,$A$2:$A$5,$A10,$B$2:$B$5,$B10)
copied down & across

(assumes per OP that month headers directly correlate between the two tables)