MATCH function displays an error on some cells but works for other

Question

Two sections of my table cannot process data because MATCH formula is displaying "Value not available" (values are present in range), for other five sections MATCH formula works.

I've tried changing ranges, cleaning/trimming range (to remove hidden chars), for some weird reason second section works when I changed lookup range from $A$2:$A$1200 to $A$2:$A$1300 but left INDEX range at $A$2:$A$1200.

=CELL("address",INDEX($A$2:$A$1200,MATCH($AP8,$A$2:$A$1200)))

Expected result would be cell reference of first matching value in ascending order from lookup range, but first two sections are displaying #N/A error.

EDIT #1:

Data example:

    A         B        C        D
1 Name1     Name1

2 Name1     Name2

3 Name1

4 Name2

5 Name2

6 Name1

7 Name2

...(list is never completed as new data is always entered)

I want cell reference of specific Name that has been entered latest. In this case it would be

 =CELL("address",INDEX(A1:A7,MATCH(B1,A1:A7)))

and expected result would be $A$6.

MATCH($AP8,$A$2:$A$1200) is doing a Nearest Match (see doc's here). You probably want MATCH($AP8,$A$2:$A$1200,0) — chris neilsen
Using 0 in MATCH function gives me back first result from the top, without 0 function displays first result from bottom of data (which i need). I would be willing to provide the file if someone wants to help. — Imran Lokmić
You are using the Match inside a Cell( Index()) construct. Please explain what you want to achieve. Maybe there is a better way. Post a few rows of sample data (no more than a dozen, keep it simple) and the expected results. Please read this carefully: Edit your post to provide this additional information. Do not put additional details into comments. Then post a comment to alert the people who are following this question. — teylyn
I've added an example, and again I would be willing to share the file if its still unclear. — Imran Lokmić

chris neilsen chris neilsen · Accepted Answer · 2019-08-25T23:32:55

To get cell reference of specific Name that has been entered latest you can't use MATCH. But you can use LOOKUP, like this

=ADDRESS(LOOKUP(2,1/(A:A=C1),ROW(A:A)),1)

MATCH function displays an error on some cells but works for other

2 Answers