CODEWITHSUNDEEP
excelindexingexcel-formulamatch
MartaT
MartaT

Reputation: 1

index match not working for a list of values starting with same text string

On a Workbook with two sheets, I need to retrive the value of "Application Group" column "B" in the first sheet into column "E" second sheet. The matching values are Configuration Items column "D" in the first sheet and column "G" in the second sheet.The matching range values are text string starting with same characters: IT-CREDIT

refer to image

If the index range is set to last row 4, the value returned for last matching range cell is #REF!

=INDEX('GR-AP'!$A$2:$J$4; MATCH(G6;'GR-AP'!$D$1:$D$20000;0);2)

refer to image

If the index range is set greater than the last row, the value returned for last matching range cell is 0

=INDEX('GR-AP'!$A$2:$J$5; MATCH(G6;'GR-AP'!$D$1:$D$20000;0);2)

refer to image

If we add a new value to the range in the first sheet, the value returned for last matching range cell is the new value: GR-TEST. The expected returned value of the matching: GR-IT-CREDIT

Any idea why this index match formula is working sort of. its only working for the first 2 matching occurences and not for the third one?

refer to image

Upvotes: 0

Views: 44

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

MATCH returns the relative position in the search range.

So

MATCH(G6;'GR-AP'!$D$1:$D$20000;0)

Is finding it on the 4th row relative to row 1

But you INDEX range only has 3 relative rows:

$A$2:$J$4

There is no Relative row 4 and is why it is returning #REF

And why when including row 5 in the index it returns the wrong return, because it wants to return the relative row not the cardinal row.

Make your Match and Index ranges start on the same row and make them the same number of rows:

=INDEX('GR-AP'!$A$1:$J$20000; MATCH(G6;'GR-AP'!$D$1:$D$20000;0);2)

Upvotes: 1

Related Questions