Index/Match returning invalid data

Jeff Chamberlain

I am trying to match the address columns from two different sheets, and pull the distance from one of them and add it to the other where the two addresses match. I am getting a invalid matches where a value is returned yet isn't in the opportunity addresses sheet.

My formula:

=INDEX({Opportunity Addresses Range 1}, MATCH(Address@row, {Opportunity Addresses Range 2}))

The frustrating part is validating the formulas is utterly useless. I cannot confirm that range 1 and range 2 are in fact the address and distance columns, and this eliminates the validations.

Paul Newcome

First try specifying an exact match:

=INDEX({Opportunity Addresses Range 1}, MATCH(Address@row, {Opportunity Addresses Range 2}, 0))

Why can you not confirm what the ranges are?

Jeff Chamberlain

Adding the , 0 did not change the values at all unfortunately.

"Why can you not confirm what the ranges are?"

If you look at that formula, can you tell me what the ranges are? In Excel for instance, I would get a table name and a header field name, in the formula. The Range 1, Range 2 tells me absolutely nothing.

Paul Newcome

If you open the formula up as if you are going to edit it, you can drop your cursor in the range and select "Edit Range". You can also right click on any cell in the sheet to open up the cell menu and select the option to Manage References. You can also rename your cross sheet references when creating them to make troubleshooting easier in the future.

Jeff Chamberlain

"You can also rename your cross sheet references when creating them to make troubleshooting easier in the future."

OK - full disclosure - I was wracking my brain on a typo that kept appearing here and a quick google showed me how to do this - I have to say you are a freaking' genius on that issue. Now with an edited formula - does anything stand out to you as incorrect here:

=INDEX({Opportunity Addresses - Distance}, MATCH(Address@row, {Opportunity Addresses - Address}), 0)

Note - Address@row is the Address field in the local row.

My use case is I want to index (or retrieve) the distance from that sheet where there is an exact match between the two fields.

Paul Newcome

Try moving that zero into the MATCH function. Right now you have it in the INDEX function. Double check the placement in my first comment.