Lookup Table

Hannahsamd

I am trying to create a report with 2 sheets. One sheet has a column that have Regions and the other sheet has the regions and the supervisors associated with those regions. In the report I want to match up regions in the first sheet with the supervisors in the second sheet. Anyone know how to make this lookup table in the report?

I have attached a screen shot of the sheet with the region and supervisor names and of the report I am trying to build (scribbled out names for privacy)

screen 2 .png

screen.png

Itai Perez

Hey,

One solution would be to use either Vlookup or Datamesh (if you have it in your plan) to combine the sheets data into 1 sheet, then you will be able to use that sheet in the report to display it the way you like.

Hannahsamd

I have tried a Vlookup but I think I am doing something wrong

=VLOOKUP([Region:Region],{TierNAM Region},4,false)

I am not sure how to connect the tables using this

Itai Perez

Hey Hannah,

Can you please attach a screenshot of both the sheets and if possible color the columns you chose for the data part of the formula?

Hannahsamd

6.png

9.png

The one labeled 6 is the spreadsheet with all of the information about region and supervisor. I am trying to put the lookup table into there

The one labeled 9 is the spreadsheet that I cannot add information too and is a master spreadsheet. I am trying to match up the Tier NAM regions with the supervisors in the first spreadsheet and assign the regions their correct person.

Matt Johnson

Hi @Hannahsamd

Can you provide an example of what you want the formula to return?

I would suggest using an INDEX MATCH formula instead of a Vlookup formula. This formula might work where "Provider Groups" and "TierNAM Region" are the named ranges; each referring to their own column on sheet 9 respectively:

=INDEX({Provider Groups},MATCH(Region@row,{TierNAM Region},0))

You can copy and paste that formula but you'll have to edit the cross sheet references.

Also though, in your attachments, 6 contains the formula pulling data from 9. 6 appears to have a unique identifier in the Region column but 9 has duplicates in the TierNAM Region. So once the formula gets working it will always pull the first find in the search. For Example: T1:Custom4, on sheet 6, will pull from Row1 on sheet 9; even though T1:Custom4 appears 4 times on sheet 9. Maybe that's ok though.

Let us know what value you are trying to get and which column you want it to come from and we'll get this one solved.

Thanks, -Matt