INDEX MATCH showing #NO MATCH when there is one

MTGoodman

I am trying to reference another sheet. The column I am trying to match is the Primary column that has hyperlinks and I am trying to match the display text. Here is my formula

=INDEX({Project Intake Sheet Range 1}, MATCH(Links@row, {Project Intake Sheet Range 5}, 0))

Range 1 is the primary column for the display text of the reference sheet. Links@row is the text on my sheet for the data to display. Range 5 is the data that I want to pull. I can add screenshots to show, but was wondering if the Hyperlink is the issue here.

Paul Newcome

So not the sheet that has the hyperlinks?

It is working for me. I put in a couple of hyperlinks and then was able to match on them.

Paul Newcome

Screenshots would be very helpful.

MTGoodman

This last screen grab is the MATCH Range

MTGoodman

Here are better screen grabs of what I'm seeing.

MTGoodman

If I take the hyperlink out, it works. Is there a way to set this up with a helper column that can use the display text (68841)?

Paul Newcome

It is because your Links column is a text value (display text) and the Work Order column is a numerical value (manual entry of a number).

Try inserting a helper column next to the Work Order column and use this to convert it into a text value:

=[Work Order]@row + ""

Then try matching on this new column.

MTGoodman

I am still coming up with #No Match. It does not like whenever I use a formula. If I use the raw numerical value it'll work.

Paul Newcome

Can you provide a screenshot of the new setup? I do this pretty regularly, so I want to make sure I explained it correctly and didn't accidentally miss a step.

MTGoodman

Yeah no problem! Here is the new helper column. It converts to text with whatever was in the Work Order column.

Paul Newcome

Ok. The first range should be the date column you are trying to pull, and the second range is the Link column you want to match on. Then the MATCH function should be looking at [Column27]@row.

MTGoodman

=INDEX({date column trying to pull over}, MATCH(Cell value i want to match, {column I want to find the match with}, 0))

Does that look right?

Is there any way I can share these sheets with you because I am fairly confident I'm doing it correctly

Paul Newcome

Can you show screenshots of the formula actually in the sheet similar to your screenshot of the helper column?

I haven't worked with hyperlinks like this much, so right now I am just assuming that they are being stored as text strings. I will do some testing of my own as well.

Edit after some testing:

What sheet is the helper column in?

MTGoodman

The helper column is in the reference sheet.

Working on pictures now

MTGoodman

Edit: Reuploaded screenshot

Paul Newcome

So not the sheet that has the hyperlinks?

It is working for me. I put in a couple of hyperlinks and then was able to match on them.

MTGoodman

EDIT: I figured it out.

I changed my formula from:

=INDEX({Project Intake Sheet Range 1}, MATCH(Links@row, {Project Intake Sheet Range 5}, 0))

to:

=INDEX({Project Intake Sheet Range 1}, MATCH(Links@row + "", {Project Intake Sheet Range 2}, 0))