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:
<tbody>
</tbody>
Main Table:
<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.
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.