Index Match (row and header) that returns the first non-zero value

The attached table image illustrates the type of data I am working with.

When I use the following formula (where A16 is SiteA and B16 is Queso), it returns 0 because that is the first value the function finds based on the criteria:

=INDEX(Table1[[Burritos]:[Tacos]],MATCH(A16,Table1[Site],0),MATCH(B16,Table1[[#Headers],[Burritos]:[Tacos]],0))

How can I find the first non-zero value that matches the criteria? The value I am looking for is 5.

Sample worksheet attached.

Without a sample workbook.

Please Login or Register  to view this content.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Try this...

Data Range

	A	B	C	D
1	Site	Burritos	Salsa	Queso
2	SiteA	2	3
3	SiteB			1
4	SiteA			5
5
6
7
8
9
10
11
12
13
14
15
16	SiteA	Queso	5

This array formula** entered in C16:

=INDEX(D2:D4,MATCH(1,(A2:A4=A16)*(INDEX(B2:D4,0,MATCH(B16,B1:D1,0))<>""),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Originally Posted by jmanp

@Tony Valko

In the outermost argument you told it to look in, or "INDEX", cells D2:D4. I need it to know which column to look in based on cell B16.

That's what the highlighted portion is doing:

=INDEX(D2:D4,MATCH(1,(A2:A4=A16)*(INDEX(B2:D4,0,MATCH(B16,B1:D1,0))<>""),0))

Good deal. Thanks for the feedback!