Help with Index Match formula with cross sheet reference

Chris Downes

Hi! I'm working on what I thought was a simple Index match formula with a cross sheet reference, but the data is not consistently coming back correctly. Some rows work while others dont.

Here is my scenario:

I have Business Areas sheet where the range is from. Intake Process sheet where I want the data to display.

Business Areas sheet has 3 columns: Business Area, Business Owner 1 and Business Owner 2. Sheet B has Business Owner 2 and Business Area. I'm trying to get the Business Owner from Business Area sheet to pull into Intake Process sheet instead of having to list it manually. Range 1 is the Business Owner 2 column from Business Areas sheet. Range 3 is all three columns from Business Areas sheet.

This is the formula: =INDEX({Business Areas Range 1}, MATCH([Business Area]@row, {Business Areas Range 3}, 0))

The Business Areas column is an exact copy of text and formatting from one sheet to the other.

Why does it only work sometimes?

Thanks for your help!

Lucas Rayala

Hi @Chris Downes, it looks okay, but there's no reason to have 3 columns in Range 3--that just introduces the possibility of error--so lets start by simplifying that. Range 3 should only be the [Business Area] column from the Business Areas sheet. Also, doublecheck that your Range 1 is pointing to the correct column.

If this doesn't do it, provide some screenshots so we can look at the actual data and see if something stands out.

Let me know if this helps, and please flag this post if I answered your question! Good luck!

Lucas Rayala

Hi @Chris Downes, it looks okay, but there's no reason to have 3 columns in Range 3--that just introduces the possibility of error--so lets start by simplifying that. Range 3 should only be the [Business Area] column from the Business Areas sheet. Also, doublecheck that your Range 1 is pointing to the correct column.

If this doesn't do it, provide some screenshots so we can look at the actual data and see if something stands out.

Let me know if this helps, and please flag this post if I answered your question! Good luck!

Chris Downes

@Lucas Rayala Thank you! that seemed to fix it!

Lucas Rayala

@Chris Downes, glad it worked!