Clash Royale CLAN TAG

Overcome #REF error using INDEX MATCH FUNCTION

I am getting reference error which I am not understanding the logic behind. Anyone please help me with this. Thanks in Advance!!

SHEET 1:

SKU Reference Type PP_1 PP_2
A A X 61.99 17.9975
A A-A Y 56.99
A A-S Y 56.99
B B X 68.99 19.7475
B B-A Y 68.99


SHEET 2:

SKU Reference Type PP_3
A A X 17.9975
A A-A Y #REF
A A-S Y #REF
B B X 19.7475
B B-A Y #REF


OUTPUT_REQUIRED:

SKU Reference Type PP_3
A A X 17.9975
A A-A Y 56.99
A A-S Y 56.99
B B X 19.7475
B B-A Y 68.99


FORMULA I TRIED:

=INDEX(Sheet1!D:E,MATCH(A5,Sheet1!A:A,0),MATCH(C5,{"X"," ","Y"},0)+AND(VLOOKUP(A5,Sheet1!A:C,3,FALSE)="X"))


3 Answers
3

In D2 sheet2 you can put

=IFERROR(IF(C2="Y",VLOOKUP(B2,Sheet1!$B$2:$E$6,3,FALSE),VLOOKUP(B2,Sheet1!$B$2:$E$6,4,FALSE)),"")


And fill down

In your formula, =INDEX(Sheet1!D:E,MATCH(A5,Sheet1!A:A,0),MATCH(C5,{"X"," ","Y"},0)+AND(VLOOKUP(A5,Sheet1!A:C,3,FALSE)="X")), your first INDEX is looking up an array (or range, or matrix, if you will) that is two columns wide (D and E). However, your MATCH-array for the column number, {"X"," ","Y"}, will give you 1 for X, 2 for space and 3 for Y. It looks like your #REF error comes from looking up the 3rd columns in a two-column wide array, which is why you only get the #REF error on Y-items. Try instead:

=INDEX(Sheet1!D:E,MATCH(A5,Sheet1!A:A,0),MATCH(C5,{"X"," ","Y"},0)+AND(VLOOKUP(A5,Sheet1!A:C,3,FALSE)="X"))

{"X"," ","Y"}

=INDEX(Sheet1!D:E,MATCH(A5,Sheet1!A:A,0),MATCH(C5,{"X","Y"},0)+AND(VLOOKUP(A5,Sheet1!A:C,3,FALSE)="X"))


I really do not understand your formula but I think that this gives you what you are looking for:

=INDEX(Sheet1!D:E,ROW(),MATCH(Sheet1!C2,{"Y","X"},0))


Since it appears you have already copied the values to the current sheet from Sheet1, you could probably shorten this to:

=INDEX(D:E,ROW(),MATCH(C2,{"Y","X"},0))


I put that in Cell D2 and copied it down the column giving me the following:

17.9975
56.99
56.99
19.7475
68.99


In particular, this part confused me from your original formula:

+AND(VLOOKUP(A2,Sheet1!A:C,3,FALSE)="X")

This is a TRUE/FALSE value that is being added to a column. It looks up the value value in the first column and returns the first value in Column C. The first instance of A is row 2 and the first instance of B is row 5, so it returns "X" for all rows with "A" (i.e., from row 2) and "X" for all rows with "B" (i.e., from row 5. Since it is always TRUE I got rid of that, you could have achieved the same thing by adding 1 instead.

I was very confused by this part as well:

MATCH(C2,{"X"," ","Y"},0)


It returns 1 for "X" 2 for " " and 3 for "Y." I think you want Column 2 for "X" and Column 1 for "Y" and I do not know which you want to return if Column C is blank. I changed this to:

MATCH(C2,{"Y","X"},0)


If there is a case that needs to be handled if Column C is blank, it was not included in the original post so I did not know how you wanted to handle it.

Since the values in A are not unique, this part is finding the first row with the current row's value of Column A, so it is finding 2 for "A" and 5 for "B" again:

MATCH(A2,Sheet1!A:A,0)


I think you could have used this instead:

MATCH(B2,Sheet1!B:B,0)


assuming that the values in B are unique but since you seem to want the current row, I changed it to ROW().

I hope this helps.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.