Index formula with matching - Can it look at rows and columns?

[usnapoleon]

Hello
I have an Index formula that currently looks for its result by matching an outlet. It's here, the Tax formula in B17

=IFERROR(INDEX(I$40:I$43,MATCH("Blue River Lounge",A$40:A$43,0)),0)

But I have a problem, let me share with you my template (top) which looks through exported data (below).

04 Audit Breck.xlsx
	A	B	C	D	E	F	G	H	I
17	Tax	60.79		Cash	0.00			Lounge	Market
18	Food	364.50		Gift Card	0.00			398.67
19	Beverages	114.50		Card	398.67				263.74
20	Alcohol	206.10		House Account	362.90
21	Activities	0.00		Other	59.84
22	Other Categories	0.00		Check	0.00
23				Credit Card Fees	12.81
24	Unclassified					Variance to Square Input "J13"
25		834.22			834.22	($0.00)
26			Variance Sales/Payment	0.00
27
28
29
30	Key Stats
31	Filtered By Location: Blue River Lounge, The Market
32	Sales	56
33	Avg. Gross Sale	$19.05
34	Gross Sales	$1,067.00
35	Total Collected	$1,188.73
36
37	Sales Summary
38	Display By Location
39	Filtered By Location: Blue River Lounge, The Market
40	Sales Summary Displayed by Location	Gross Sales	Items	Service Charges	Refunds	Discounts & Comps	Net Sales	Gift Card Sales	Tax
41	The Market	$343.00	######	$0.00	$0.00	($17.40)	$325.60	$0.00	$28.91
42	Blue River Lounge	$724.00	######	$0.00	$0.00	($38.90)	$685.10	$0.00	$60.79
28

Cell Formulas
Range	Formula
H18	H18	='28'!E19
I19	I19	='28'!K7
E17:E18,E20:E21	E17	=IFERROR(INDEX(J$30:J$80,MATCH(1,(A$30:A$80="Blue River Lounge")*(B$30:B$80=D17),0)),0)
E19	E19	=SUM(FILTER($J$30:$J$80,(LEFT($B$30:$B$80,4)=D19)*($A$30:$A$80=TEXTAFTER($A$15," ",1)),0))+SUMIFS($J$30:$J$80,$B$30:$B$80,"Cash App",$A$30:$A$80,"blue river lounge")
B17	B17	=IFERROR(INDEX(I$40:I$43,MATCH("Blue River Lounge",A$40:A$43,0)),0)
B18:B22	B18	=IFERROR(INDEX(H$30:H$90,MATCH(1,(A$30:A$90="Blue River Lounge")*(B$30:B$90=A18),0)),0)
E23	E23	=IFERROR(-INDEX(M$40:M$43,MATCH("Blue River Lounge",A$40:A$43,0)),0)
B25,E25	B25	=SUM(B16:B24)
F25	F25	=IFERROR(E25-INDEX(L$40:L$43,MATCH("Blue River Lounge",A$40:A$43,0)),0)
D26	D26	=B25-E25

My problem is - on occasion - the export gets modified. I cannot control that. It will add columns, which shoves over the existing columns. This means, the column I indicated as the tax amounts... column I in this case... could change to column J or whatever. I have to manually update the formulas. Today, it happened. Tax used to be column G, now it's I. No warning, just happens.

I was wondering if it were possible to modify this formula to look at a range of columns (instead of a single column). It will give a result based on matching the row's identifying outlet (such as 'Blue River Lounge' in A42) and also matching the column's name (such as Tax in I40) with the description in the template (A17). This way, if the numbers get shuffled around, it won't matter... Tax could be in column J or column D, the formula would still look for a column in row 40 that matches the name in A17, and give the results that also match it to the outlet.

I've done multiple matches before, but it's always based on single column matches, like this =IFERROR(INDEX(J$30:J$80,MATCH(1,(A$30:A$80="Blue River Lounge")*(B$30:B$80=D17),0)),0) I've never had a range that expands over multiple columns, and I dont know if it's possible.

 

[Anthony47]

You correctly realized that you need a double Match; for example:

=IFERROR(INDEX($A$40:$Z$42,MATCH("Blue River Lounge",$A$40:$A$42,0),MATCH(A17,$A$40:$Z$40,0)),0)

This assumes that A17 contains "Tax"

Try...

 

[usnapoleon]

You correctly realized that you need a double Match; for example:

=IFERROR(INDEX($A$40:$Z$42,MATCH("Blue River Lounge",$A$40:$A$42,0),MATCH(A17,$A$40:$Z$40,0)),0)

This assumes that A17 contains "Tax"

Try...

Sweet!!! Thank you!!!!! it works like a charm, and I understood the coding!