Match and Index

[rhmkrmi]

Hi there, I have created the following formula but it does not return the correct row number:

MATCH(INDEX(List!G1:G1000000,MATCH(P4,List!H1:H1000000,0)),Ref!W1:W1000000)

In sheet Ref, the row number that it should return is 38748 but it returns 38866.

The value is a number that is in row 38748 in column W.

When I try MATCH(INDEX(List!G:G,MATCH(P4,List!H:H,0)),Ref!W:W) it returns 36071 which is also wrong.

Thank you!

 

[AlanY]

try

MATCH(INDEX(List!G1:G1000000,MATCH(P4,List!H1:H1000000,0)),Ref!W1:W1000000,0)

 

[rhmkrmi]

Thanks but that returns #N/A

 

[AlanY]

#N/A means no match.

can you confirm if the content in P4 also in List!G:G and Ref!W:W

 

[rhmkrmi]

There is a match in row 38748 in sheet "Ref" column W.

P4 is in sheet "List" in row 18 column H and the values is matched in column G but when I try to match the value of column G sheet "List" in column W sheet "Ref", it returns #N/A using your version in post #2 .

Interestingly, when I hard code the lookup_value, it does find the exact match in sheet "Ref" column W as in MATCH(23595,Ref!W1:W1000000,0)

Thanks for your help again

 

[AlanY]

There is a match in row 38748 in sheet "Ref" column W.

P4 is in sheet "List" in row 18 column H and the values is matched in column G but when I try to match the value of column G sheet "List" in column W sheet "Ref", it returns #N/A using your version in post #2 .

Interestingly, when I hard code the lookup_value, it does find the exact match in sheet "Ref" column W as in MATCH(23595,Ref!W1:W1000000,0)

Thanks for your help again

so, it's likely that the 23595 in P4 has extra space in front or after. or formatted as text
try test in with len(P4), and
isnumber(P4)

 

[mail2master]

Are there Decimal Values as well in the array ???
Any possibilities to have Duplicate Values ??
Did you apply TRIM() yet.

 

[rhmkrmi]

They are all numbers and there is no text or space but there are decimals and there are duplicates but in this particular example there is no duplicate.

I do the following:

MATCH(P4,List!H1:H1000000,0) and the result is correctly 18
INDEX(List!G1:G1000000,MATCH(P4,List!H1:H1000000,0)) and the result is correctly 23595
MATCH(INDEX(List!G1:G1000000,MATCH(P4,List!H1:H1000000,0)),Ref!W1:W1000000,0) and the result is incorrectly #N/A while it should be 38748

If I do MATCH(23595,Ref!W1:W1000000,0) I correctly get 38748

 

[rhmkrmi]

Can anyone help with this please?

Thank you.