Need the cell reference for a cell with unknown row and known column

Question

I am trying take an ID in ColumnX and verify find an occurrence of this ID in columnY using the MATCH() function. This function returns a row number of the match. I now need to take the row number and combine it with a ColumnZ in order to make a reference to a value where I later run a comparison on this value.

Here is the code I have so far:

IF(EXACT(MATCH(X:X, Y:Y, 0), Z:Z), "Y", "N")

The bug in this code is where I am passing the parameters into the EXACT function. It wants two strings (i.e. cell values), only the Z:Z statement correctly satisfies this, is there anyway to achieve something like this:

IF(EXACT(("Z" + MATCH(X:X, Y:Y, 0)), Z:Z), "Y", "N")

I am simply trying to create a cell reference from a known Column number (what I don't know how to do) and an unknown row number (produced by the MATCH function).

I have already tried using the Vlookup function and it does not produce the desired results.

Answer 1

Have you tried to use INDIRECT?

I believe that's the function you might need. You'll need to check now where you need to add it. I'd guess it's here:

=IF(EXACT(INDIRECT("Z" & MATCH(X:X, Y:Y, 0)), Z:Z), "Y", "N")

Rgds

Answer 2

Mike,

If I understand your needs, the following will work. It says, "find the row in column Y that matches the value in X1 and return the value at that row in column Z":

=INDEX(Z:Z,MATCH(X1,Y:Y,0))

The 0 argument in the Match function specifies an exact match, meaning that column Y doesn't need to be sorted (similar to the optional False argument in VLookup).