Relative reference not working on INDEX/MATCH with Multiple Criteria

[TXRChav]

Hello,

This is my first time posting so even though I read the rules, please forgive me if I accidentally break one or two.

I am currently using excel 2013 and am trying to complete a lookup using Index/Match on two criteria. I am able to complete one row, but the formula does not update the relative reference and just gives me the results for the 1 row. My example tables are below. My actual spreadsheets are pretty big so please forgive the fact that the rows references in the examples aren't actually present in the example.

Reference Table:

QAWO_ID	PW_QA_CONTROL_NUMBER__C	ORDER_DETAIL_ID__C
a1g140000012K9qAAE	a1Y140000006pWpEAI
a1g140000012K9vAAE	a1Y14000001dPotEAE	a1b1400000Ai3Ku
a1g140000012KA0AAM	a1Ya0000001d4qiEAA	a1ba000000AbCyM
a1g140000012KA5AAM	a1Ya0000001d2GtEAI	a1b1400000AhwQ2
a1g140000012KAAAA2	a1Ya0000001d3NgEAI	a1ba000000ALjCQ
a1g140000012KAFAA2	a1Y140000006pXJEAY
a1g140000012KAKAA2	a1Y140000006pXOEAY
a1g140000012KAPAA2	a1Ya0000001d1YfEAI
a1g140000012KAUAA2	a1Y140000006pXTEAY
a1g140000012KAZAA2	a1Y140000006pXYEAY
a1g140000012KAeAAM	a1Y140000006pXdEAI
a1g140000012KAjAAM	a1Y140000006pXiEAI
a1g140000012KAoAAM	a1Y140000006pXnEAI
a1g140000012KAtAAM	a1Y140000006pXsEAI

<tbody>
</tbody>

Main Table:

ID	PW_INVENTORYLK__C	PW_ORDER_DETAILLK__C		QAWO_ID	Match Inventory	Match Order Detail
a1u140000005UYMAA2		a1b1400000BMeqqAAD	a1b1400000BMeqq	#N/A	#N/A	29241
a1u140000005UiCAAU		a1ba0000008kwUaAAI	a1ba0000008kwUa	#N/A	#N/A	#N/A
a1u140000005UiHAAU		a1b1400000BWKosAAH	a1b1400000BWKos	#N/A	#N/A	31089
a1u140000005UiIAAU	a1Y140000024m8iEAA	a1b1400000BWKosAAH	a1b1400000BWKos	#N/A	15442	31089
a1u140000005UjPAAU		a1b1400000BVhdlAAD	a1b1400000BVhdl	#N/A	#N/A	#N/A
a1u140000005UjoAAE	a1Y14000000A4ANEA0	a1b1400000BWQznAAH	a1b1400000BWQzn	#N/A	31090	31090
a1u140000005UlGAAU	a1Ya0000001d2AlEAI	a1b1400000BMKWzAAP	a1b1400000BMKWz	#N/A	1326	31092
a1u140000005UlLAAU	a1Ya0000001d2B0EAI	a1b1400000BVhhdAAD	a1b1400000BVhhd	#N/A	2644	31093
a1u140000005UlQAAU		a1b1400000BLPRcAAP	a1b1400000BLPRc	#N/A	#N/A	29716

<tbody>
</tbody>

My issue is on column QAWO_ID on the main table. The formula I am using is:

{=INDEX(qaWO_extract_oct11!$A$2:$C$114721,MATCH(1,(transevents_extract_oct11!B2=qaWO_extract_oct11!$B$2:$B$114721)*(transevents_extract_oct11!D2=qaWO_extract_oct11!$C$2:$C$114721),0),1)}

I have underlined a row in the Main Table where the formula should return a match, but the reference in the Lookup_array portion of my nested match function is always referencing my first row. If I input B7/D7 into my match it works, but all my rows show the result for that one row. I have seen plenty of examples where this should become a relative reference, but it's just not working.

A simplified version of my formula is:

{=INDEX('table1'$A$1:$C$10,MATCH(1,('table2'!B2='table1'$B$1:$B$10)*('table2'!D2='table1'$C$1:$C$10),0),1)}

I am expecting the B2 and D2 references from table 2 to reference B3/D3 in row 3 and B4/D4 in row 4, etc., but all my rows reference B2/D2.


I hope this makes sense and someone can answer my questions. Thanks in advance.

 

[Sinon]

How did you apply your formula to the other cells? try flash filling (https://support.office.com/en-us/ar...ash-Fill-2e79a709-c814-4b27-8bc2-c4dc84d49464) or copy/pasting the cell with the formula (not just the formula) in the other rows.

You can also use this formula, which is a slight variation on yours, but does the same thing:
=(INDEX(qaWO_extract_oct11!$A$2:$A$15,MATCH(B2&D2,qaWO_extract_oct11!$B$2:$B$15&qaWO_extract_oct11!$C$2:$C$15,0)))
Don't forget to confirm with CTRL+SHIFT+ENTER not just ENTER.

 

[TXRChav]

Thanks for the suggestion.

I had not seen that version of the formula. I will try it next time. It seems easier to read and keep track of.

Yesterday I was receiving a message when trying auto-fill saying that an array could not be altered. I even tried selecting my range and then entering my formula into the entire range. I am not sure why it works, but today the formula worked. I did the following:

1. Entered my formula as:

=INDEX(qaWO_extract_oct11!$A$2:$A$114721,MATCH(1,(qaWO_extract_oct11!$B$2:$B$114721=transevents_extract_oct11!B7)*1,0))

without Ctrl+Shift+Enter then used autofill

2. Re-selected the formula and used Ctrl + Shift + Enter followed by another auto-fill.
(At this point the formula worked and I received the index value for all ID's matching inventory.)

3. Re-selected the formula and replaced the 1 with the second equivalency test:

=INDEX(qaWO_extract_oct11!$A$2:$A$114721,MATCH(1,(qaWO_extract_oct11!$B$2:$B$114721=transevents_extract_oct11!B7)*(qaWO_extract_oct11!$C$2:$C$114721=transevents_extract_oct11!D7),0))

used Ctrl + Shift + Enter and autofill.


After crashing Excel, closing everything on my computer to free up processing, and waiting 15 minutes for it to finish recalculating, the cells successfully returned the correct values.


I would like to understand this a little better to prevent headaches in the future.

 

[Sinon]

Sounds like this formula might be a bit too much for excel. I occasionally get a long calculation time when I use more than 1 criteria, like you are doing here. A possible workaround would be to add helper columns and use those o match.
Helper column in your "qaWO_extract_oct11" sheet:

*	A	B	C	D
1	QAWO_ID	PW_QA_CONTROL_NUMBER__C	ORDER_DETAIL_ID__C	Helper column
2	a1g140000012K9qAAE	a1Y140000006pWpEAI	*	a1Y140000006pWpEAI
3	a1g140000012K9vAAE	a1Y14000001dPotEAE	a1b1400000Ai3Ku	a1Y14000001dPotEAEa1b1400000Ai3Ku
4	a1g140000012KA0AAM	a1Ya0000001d4qiEAA	a1ba000000AbCyM	a1Ya0000001d4qiEAAa1ba000000AbCyM
5	a1g140000012KA5AAM	a1Ya0000001d2GtEAI	a1b1400000AhwQ2	a1Ya0000001d2GtEAIa1b1400000AhwQ2
6	a1g140000012KAAAA2	a1Ya0000001d3NgEAI	a1ba000000ALjCQ	a1Ya0000001d3NgEAIa1ba000000ALjCQ
7	a1g140000012KAFAA2	a1Y140000006pXJEAY	*	a1Y140000006pXJEAY
8	a1g140000012KAKAA2	a1Y140000006pXOEAY	*	a1Y140000006pXOEAY
9	a1g140000012KAPAA2	a1Ya0000001d1YfEAI	*	a1Ya0000001d1YfEAI
10	a1g140000012KAUAA2	a1Y140000006pXTEAY	*	a1Y140000006pXTEAY
11	a1g140000012KAZAA2	a1Y140000006pXYEAY	*	a1Y140000006pXYEAY
12	a1g140000012KAeAAM	a1Y140000006pXdEAI	*	a1Y140000006pXdEAI
13	a1g140000012KAjAAM	a1Y140000006pXiEAI	*	a1Y140000006pXiEAI
14	a1g140000012KAoAAM	a1Y140000006pXnEAI	*	a1Y140000006pXnEAI
15	a1g140000012KAtAAM	a1Y140000006pXsEAI	*	a1Y140000006pXsEAI

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D2 =B2&C2 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Helper column and results in your "transevents_extract_oct11" sheet:
<title>Excel Jeanie HTML</title>

*	A	B	C	D	E	F	G	H
1	ID	PW_INVENTORYLK__C	PW_ORDER_DETAILLK__C	*	Helper column	QAWO_ID	Match Inventory	Match Order Detail
2	a1u140000005UYMAA2	*	a1b1400000BMeqqAAD	a1b1400000BMeqq	a1b1400000BMeqq	#N/A	#N/A	29241
3	a1u140000005UiCAAU	*	a1ba0000008kwUaAAI	a1ba0000008kwUa	a1ba0000008kwUa	*	#N/A	#N/A
4	a1u140000005UiHAAU	*	a1b1400000BWKosAAH	a1b1400000BWKos	a1b1400000BWKos	*	#N/A	31089
5	a1u140000005UiIAAU	a1Y140000024m8iEAA	a1b1400000BWKosAAH	a1b1400000BWKos	a1Y140000024m8iEAAa1b1400000BWKos	*	15442	31089
6	a1u140000005UjPAAU	*	a1b1400000BVhdlAAD	a1b1400000BVhdl	a1b1400000BVhdl	*	#N/A	#N/A
7	a1u140000005UjoAAE	a1Y14000000A4ANEA0	a1b1400000BWQznAAH	a1b1400000BWQzn	a1Y14000000A4ANEA0a1b1400000BWQzn	*	31090	31090
8	a1u140000005UlGAAU	a1Ya0000001d2AlEAI	a1b1400000BMKWzAAP	a1b1400000BMKWz	a1Ya0000001d2AlEAIa1b1400000BMKWz	*	1326	31092
9	a1u140000005UlLAAU	a1Ya0000001d2B0EAI	a1b1400000BVhhdAAD	a1b1400000BVhhd	a1Ya0000001d2B0EAIa1b1400000BVhhd	*	2644	31093
10	a1u140000005UlQAAU	*	a1b1400000BLPRcAAP	a1b1400000BLPRc	a1b1400000BLPRc	*	#N/A	29716

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E2 =B2&D2 F2 {=INDEX(qaWO_extract_oct11!A:A,MATCH(transevents_extract_oct11!E2,qaWO_extract_oct11!D:D,0))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

You can also use a VLOOKUP which is even simpler, but you need to add the helper column in your source table as the first column, in A:A not in D:D where I added it.

Array formulas tend to take up more resources than non-array ones, and multiple criteria in a big cell range will make you want to pull your hair out.

<!-- ######### End Created Html Code To Copy ########## -->

 

[Jonmo1]

I think Simon was onto the problem about 'how you enter the formula into the cells'

When entering the array formula, don't highlight ALL the cells you want to put the formula in, and then press CTRL + SHIFT + ENTER.
Instead, put the formuls in just the top cell, press CTRL + SHIFT + Enter
THEN fill down.