Index function with hlookup to display values located on another page

**PhoenixFaery** · 06-17-2013, 11:13 PM

Hi, I have a GroupsTable located on the Tiers page with the following data...
Columns = GroupID,Tier1,Tier2,Tier3
Rows
Row1 = 0, Erin Miller, "", ""
Row2 = 1, Edward Lee, "", ""
Row3 = 2, Tamara Simmons
Row4 = 3, Ted Callaghan, Erin Miller, ""
Row5 = 4, Bill Turner, Ted Callaghan, Erin Miller

I want to display the value of INDEX(Groups,5,4) in a cell on the Sales-Commission page only after checking that row 4 column 2 value matches, and that the row 4 column 3 value matches, otherwise return "". I would appreciate any assistance as to the best way to accomplish this. I am stumped on this one.

**Crawfinator1** · 06-18-2013, 01:18 AM

upload an example so we can help

**PhoenixFaery** · 06-18-2013, 08:14 AM

Thank you for replying Crawfinator1. Here is the example file.

**PhoenixFaery** · 06-18-2013, 10:26 AM

Basically, I want to check the Tier 1 Sales Rep and Tier 2 Sales Rep values for each sale entered, match them to the tier group and automatically display the Tier 3 Sales Rep on the Sales-Commission page in that column. If there is no Tier 3 Sales Rep, then display a blank cell.

**PhoenixFaery** · 06-18-2013, 02:10 PM

When I use =INDEX(Groups[Tier3],MATCH(F19,Groups[Tier1],0)),IF(Groups[Tier3]=0,"",Groups[Tier3]) the return is #value in the Tier 3 Sales Rep column.

**PhoenixFaery** · 06-18-2013, 02:19 PM

Solved with =INDEX(Groups[Tier3],MATCH(F19,Groups[Tier1],0))

**Crawfinator1** · 06-18-2013, 06:22 PM

glad i could help!

**PhoenixFaery** · 06-18-2013, 09:10 PM

Thanks for helping out Crawfinator

. After beating my head against a wall trying to solve this, I found a video that Mike Girvin did on vlookup week where he mentioned the index and match functions as a work around to the #NA error you get with vlookup when the reference cell is to the left of the vlookup formula cell, which in this case, mine was. The index match solved two issues at once because it also was the solution to indexing the tier sales reps.

w-to/