VLOOKUP(Index/Match) data from another sheet, using 2 separate dropdowns

[Will_OKC]

I've looked at several index/match and index/indirect formulas here but I cannot wrap my head around this. I have all data on 1 sheet, but need to show the results from the formula on another sheet. Here is what I am working with:

Sheet Analysts has all the data I need.
Column A is AGENT ID: The list of names (i.e MSMITH)
Columns B - Z are different metrics I need to view (labeled Accuracy, # of Calls, Tenure) etc etc. .. a very simple table.

For example, on Sheet2 labeled Seating, I have two dropdowns - AGENT ID and Metric. I want the years of tenure to populate in a specific cell (see below)
The closest I feel I've gotten to get it working is using this: =INDEX(INDIRECT("Analysts!["&$G$16&"]"),MATCH($I$19,Analysts![AGENT ID],0)).

To clarify, the tenure data for MSMITH would be located in Analysts Z38. If I change the Metric to say, Accuracy, it would be Analysts P38.

Please help!

-- corrupted image removed --

 

[Will_OKC]

Sorry.. not sure what happened with the screenshot;

 

[Will_OKC]

Looks I finally messed around with it enough .... =INDEX(INDIRECT("Analysts!Table8["&$G$16&"]"),MATCH($I$19,Table8[AGENT ID],0)) is what I was looking for. Not sure why the table was named 8, but whatever.

I do have a new issue though. Some values from Analysts are Time, Percentages, and whole numbers. Whats the best way to capture the formatting of the cell its finding ? Otherwise the tenure value is showing as 7:12 for example.

 

[NiMip]

Howdy, I've actually never done much with Tables, but you might be getting an error because you're using the name of the sheet, when you should be using the name of the table? I had a bit of a play in excel and maybe this would work (noting that the name of your table might not be Table1):

=INDEX(INDIRECT("Table1["&$G$16&"]"),MATCH($I$19,Table1[AGENT ID],0))

 

[NiMip]

Ah seems I was a few minutes too late haha. Good question about the formatting... I'll have a think.

 

[NiMip]

Looks I finally messed around with it enough .... =INDEX(INDIRECT("Analysts!Table8["&$G$16&"]"),MATCH($I$19,Table8[AGENT ID],0)) is what I was looking for. Not sure why the table was named 8, but whatever.

I do have a new issue though. Some values from Analysts are Time, Percentages, and whole numbers. Whats the best way to capture the formatting of the cell its finding ? Otherwise the tenure value is showing as 7:12 for example.

I think it's only going to be possible with either a Macro (which I'm a bit of a novice at) or a bunch of conditional formatting rules based on the Metric you're looking up. For the latter, you could set up a conditional formatting formula like:

=OR($G$16="# Calls",$G$16="Years of Tenure",$G$16="Age", etc. etc.)

And then pick the integer format. For those that should be percentages, list all the Metrics in the same way and pick the percentage format for that rule. And so on. It's time consuming and a bit clumsy, but it should work.