Relative reference not working on INDEX/MATCH with Multiple Criteria

T

TXRChav

New Member
Joined
Nov 5, 2015
Messages
10
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:


[TABLE="width: 513"]
<tbody>[TR]
[TD]QAWO_ID[/TD]
[TD]PW_QA_CONTROL_NUMBER__C[/TD]
[TD]ORDER_DETAIL_ID__C[/TD]
[/TR]
[TR]
[TD]a1g140000012K9qAAE[/TD]
[TD]a1Y140000006pWpEAI[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a1g140000012K9vAAE[/TD]
[TD]a1Y14000001dPotEAE[/TD]
[TD]a1b1400000Ai3Ku[/TD]
[/TR]
[TR]
[TD]a1g140000012KA0AAM[/TD]
[TD]a1Ya0000001d4qiEAA[/TD]
[TD]a1ba000000AbCyM[/TD]
[/TR]
[TR]
[TD]a1g140000012KA5AAM[/TD]
[TD]a1Ya0000001d2GtEAI[/TD]
[TD]a1b1400000AhwQ2[/TD]
[/TR]
[TR]
[TD]a1g140000012KAAAA2[/TD]
[TD]a1Ya0000001d3NgEAI[/TD]
[TD]a1ba000000ALjCQ[/TD]
[/TR]
[TR]
[TD]a1g140000012KAFAA2[/TD]
[TD]a1Y140000006pXJEAY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a1g140000012KAKAA2[/TD]
[TD]a1Y140000006pXOEAY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a1g140000012KAPAA2[/TD]
[TD]a1Ya0000001d1YfEAI[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a1g140000012KAUAA2[/TD]
[TD]a1Y140000006pXTEAY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a1g140000012KAZAA2[/TD]
[TD]a1Y140000006pXYEAY[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a1g140000012KAeAAM[/TD]
[TD]a1Y140000006pXdEAI[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a1g140000012KAjAAM[/TD]
[TD]a1Y140000006pXiEAI[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a1g140000012KAoAAM[/TD]
[TD]a1Y140000006pXnEAI[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a1g140000012KAtAAM[/TD]
[TD]a1Y140000006pXsEAI[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Main Table:

[TABLE="width: 1028"]
<tbody>[TR]
[TD]ID[/TD]
[TD]PW_INVENTORYLK__C[/TD]
[TD]PW_ORDER_DETAILLK__C[/TD]
[TD][/TD]
[TD]QAWO_ID[/TD]
[TD]Match Inventory[/TD]
[TD]Match Order Detail[/TD]
[/TR]
[TR]
[TD]a1u140000005UYMAA2[/TD]
[TD][/TD]
[TD]a1b1400000BMeqqAAD[/TD]
[TD]a1b1400000BMeqq[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]29241[/TD]
[/TR]
[TR]
[TD]a1u140000005UiCAAU[/TD]
[TD][/TD]
[TD]a1ba0000008kwUaAAI[/TD]
[TD]a1ba0000008kwUa[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]a1u140000005UiHAAU[/TD]
[TD][/TD]
[TD]a1b1400000BWKosAAH[/TD]
[TD]a1b1400000BWKos[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]31089[/TD]
[/TR]
[TR]
[TD]a1u140000005UiIAAU[/TD]
[TD]a1Y140000024m8iEAA[/TD]
[TD]a1b1400000BWKosAAH[/TD]
[TD]a1b1400000BWKos[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]15442[/TD]
[TD="align: right"]31089[/TD]
[/TR]
[TR]
[TD]a1u140000005UjPAAU[/TD]
[TD][/TD]
[TD]a1b1400000BVhdlAAD[/TD]
[TD]a1b1400000BVhdl[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]a1u140000005UjoAAE[/TD]
[TD]a1Y14000000A4ANEA0[/TD]
[TD]a1b1400000BWQznAAH[/TD]
[TD]a1b1400000BWQzn[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]31090[/TD]
[TD="align: right"]31090[/TD]
[/TR]
[TR]
[TD]a1u140000005UlGAAU[/TD]
[TD]a1Ya0000001d2AlEAI[/TD]
[TD]a1b1400000BMKWzAAP[/TD]
[TD]a1b1400000BMKWz[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]1326[/TD]
[TD="align: right"]31092[/TD]
[/TR]
[TR]
[TD]a1u140000005UlLAAU[/TD]
[TD]a1Ya0000001d2B0EAI[/TD]
[TD]a1b1400000BVhhdAAD[/TD]
[TD]a1b1400000BVhhd[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]2644[/TD]
[TD="align: right"]31093[/TD]
[/TR]
[TR]
[TD]a1u140000005UlQAAU[/TD]
[TD][/TD]
[TD]a1b1400000BLPRcAAP[/TD]
[TD]a1b1400000BLPRc[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: right"]29716[/TD]
[/TR]
</tbody>[/TABLE]


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.
 
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
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:

*ABCD
QAWO_IDPW_QA_CONTROL_NUMBER__CORDER_DETAIL_ID__CHelper column
a1g140000012K9qAAEa1Y140000006pWpEAI*a1Y140000006pWpEAI
a1g140000012K9vAAEa1Y14000001dPotEAEa1b1400000Ai3Kua1Y14000001dPotEAEa1b1400000Ai3Ku
a1g140000012KA0AAMa1Ya0000001d4qiEAAa1ba000000AbCyMa1Ya0000001d4qiEAAa1ba000000AbCyM
a1g140000012KA5AAMa1Ya0000001d2GtEAIa1b1400000AhwQ2a1Ya0000001d2GtEAIa1b1400000AhwQ2
a1g140000012KAAAA2a1Ya0000001d3NgEAIa1ba000000ALjCQa1Ya0000001d3NgEAIa1ba000000ALjCQ
a1g140000012KAFAA2a1Y140000006pXJEAY*a1Y140000006pXJEAY
a1g140000012KAKAA2a1Y140000006pXOEAY*a1Y140000006pXOEAY
a1g140000012KAPAA2a1Ya0000001d1YfEAI*a1Ya0000001d1YfEAI
a1g140000012KAUAA2a1Y140000006pXTEAY*a1Y140000006pXTEAY
a1g140000012KAZAA2a1Y140000006pXYEAY*a1Y140000006pXYEAY
a1g140000012KAeAAMa1Y140000006pXdEAI*a1Y140000006pXdEAI
a1g140000012KAjAAMa1Y140000006pXiEAI*a1Y140000006pXiEAI
a1g140000012KAoAAMa1Y140000006pXnEAI*a1Y140000006pXnEAI
a1g140000012KAtAAMa1Y140000006pXsEAI*a1Y140000006pXsEAI

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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>

*ABCDEFGH
IDPW_INVENTORYLK__CPW_ORDER_DETAILLK__C*Helper columnQAWO_IDMatch InventoryMatch Order Detail
a1u140000005UYMAA2*a1b1400000BMeqqAADa1b1400000BMeqqa1b1400000BMeqq#N/A#N/A
a1u140000005UiCAAU*a1ba0000008kwUaAAIa1ba0000008kwUaa1ba0000008kwUa*#N/A#N/A
a1u140000005UiHAAU*a1b1400000BWKosAAHa1b1400000BWKosa1b1400000BWKos*#N/A
a1u140000005UiIAAUa1Y140000024m8iEAAa1b1400000BWKosAAHa1b1400000BWKosa1Y140000024m8iEAAa1b1400000BWKos*
a1u140000005UjPAAU*a1b1400000BVhdlAADa1b1400000BVhdla1b1400000BVhdl*#N/A#N/A
a1u140000005UjoAAEa1Y14000000A4ANEA0a1b1400000BWQznAAHa1b1400000BWQzna1Y14000000A4ANEA0a1b1400000BWQzn*
a1u140000005UlGAAUa1Ya0000001d2AlEAIa1b1400000BMKWzAAPa1b1400000BMKWza1Ya0000001d2AlEAIa1b1400000BMKWz*
a1u140000005UlLAAUa1Ya0000001d2B0EAIa1b1400000BVhhdAADa1b1400000BVhhda1Ya0000001d2B0EAIa1b1400000BVhhd*
a1u140000005UlQAAU*a1b1400000BLPRcAAPa1b1400000BLPRca1b1400000BLPRc*#N/A

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]29241[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]31089[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]15442[/TD]
[TD="align: right"]31089[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]31090[/TD]
[TD="align: right"]31090[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]1326[/TD]
[TD="align: right"]31092[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]2644[/TD]
[TD="align: right"]31093[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]29716[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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 ########## -->
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Evaluate cell or have cell refer to itself after being transposed via. lookup functions
Replies
2
Views
116
Sam_D_Ben
Sam_D_Ben
O
  • Solved
Index/Match, Nested IF statements, or Array Formula?
Replies
9
Views
616
Ol Reliable
O
M
  • Solved
Index and Match
Replies
3
Views
230
Peter_SSs
Peter_SSs
szita2000
  • Question Question
Index Match Match from a named table to a range
Replies
1
Views
1K
Alex Blakenburg
A
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
459
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,226,875
Messages
6,193,455
Members
453,801
Latest member
777nycole

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top