Formula using Index and Match to reference another sheet only returns first item

petef

I am using an Index/Match formula to lookup a person associated with a specific region. I have a sheet setup as a lookup table named Gift Card Approver with 2 columns: Region and Approver. In my main sheet I have a formula that pulls the region from the current line and uses that to lookup the approver in the Gift Card Approver table.

The formula looks like this: =INDEX({GC Consulting Approvers 2}, MATCH(Region@row, {GC Consulting Approvers 2}, 1), 2)

The Gift Card Approver table looks like this:

The main sheet looks like this:

If you look at the region in the main lines 1 and 2 we have different regions, but they are pulling the same contact name (BTW the approver name and the consulting name are both contact columns), but they have different regions and should have different approvers.

In troubleshooting this I created 2 variations on the formula.

Using this formula in Approver Test1 to show the region instead of the contact: =INDEX({GC Consulting Approvers 2}, MATCH(Region@row, {GC Consulting Approvers 2}, 1), 1) . Note the last argument is a 1 instead of a 2. We see that the region returned does not match the region used for the search.

In Approver Test2 I modified the formula to only show the Match portion and hard coded in the region search item with the actual text of the search: =MATCH("APAC Korea", {GC Consulting Approvers 2}, 1) . It returns a 1 which is not the row that actually matches the search.

I am really scratching my head here as to why the Match function is returning a 1.

Jgorsich

@petef - Makes sense, so what it is telling you is that it isn't finding the value you are looking for at all. I think you now need to check for a couple of other issues.

The first is that the strings aren't completely identical - check this with just a simple if statement against the value you are expecting it to identify and see if it reports them as being the same. If it doesn't think they match, obviously you need to fix that.

The second is that it looks like you are trying to index through a reference to the gift card approver table that spans multiple columns - I've seen that have issues in the past, trying modifying your formula so that you are only interacting with a single column at each step of your formula, like this:

=INDEX({GC Consulting Approvers_ConsultingName}, MATCH(Region@row, {GC Consulting Approvers_Regions}, 1))

Where {GC Consulting Approvers_ConsultingName} is just the consulting name column and {GC Consulting Approvers_Regions} is just the regions column from your consulting approvers page.

Also, just in general, I'd recommend AGAINST using the default names used when making a reference to another sheet - it make it a lot easier to troubleshoot problems if you rename those references to be more descriptive.

petef

@Jgorsich Thanks for all the help. It turns out that the Approver sheet had a ZWSP character at the end of each line. This is a Zero Width Space that is sometimes used for line breaks. I have removed all of them and everything is working now.

Jgorsich

https://help.smartsheet.com/function/match

The last argument is for how the lookup column is sorted - unless you are specifically trying to take advantage of that (with maybe saying "find the last dollar value before this specific price" for a list of discount percentages based on the price of an item), just go with "0" as that argument. By telling Smartsheet that you column isn't sorted you are ALSO saying "find the exact value or toss an error, don't just try to get close".

petef

@Jgorsich thanks for the advice. If I do that, I get NO MATCH from the MATCH function. Both Region columns are text/number. The only difference in them is the one in the Gift Card Approver table is a Primary column, but that should not make a difference.

Jgorsich

@petef - Makes sense, so what it is telling you is that it isn't finding the value you are looking for at all. I think you now need to check for a couple of other issues.

The first is that the strings aren't completely identical - check this with just a simple if statement against the value you are expecting it to identify and see if it reports them as being the same. If it doesn't think they match, obviously you need to fix that.

The second is that it looks like you are trying to index through a reference to the gift card approver table that spans multiple columns - I've seen that have issues in the past, trying modifying your formula so that you are only interacting with a single column at each step of your formula, like this:

=INDEX({GC Consulting Approvers_ConsultingName}, MATCH(Region@row, {GC Consulting Approvers_Regions}, 1))

Where {GC Consulting Approvers_ConsultingName} is just the consulting name column and {GC Consulting Approvers_Regions} is just the regions column from your consulting approvers page.

Also, just in general, I'd recommend AGAINST using the default names used when making a reference to another sheet - it make it a lot easier to troubleshoot problems if you rename those references to be more descriptive.

petef

@Jgorsich Thanks for all the help. It turns out that the Approver sheet had a ZWSP character at the end of each line. This is a Zero Width Space that is sometimes used for line breaks. I have removed all of them and everything is working now.

Jgorsich

Glad to hear it worked out @petef !