1

I am creating a recruiting funnel that has two dropdown lists on the "dashboard" sheet that references a data table on another sheet. The dropdown lists are "location" and "job title". The other sheet called "Data", has a table with AREA_TYPE (US, State, Region), AREA_TITLE (name of state or region), Job_Profile (job title), and Market (total headcount in that market) among other columns. I need to be able to return the market number based on the selections made in both drop down lists from the dashboard sheet. For example, if I select "Accountant" from the profile dropdown and "Alabama" from the location dropdown, it should return only the number of accountants in Alabama. If I leave the "Accountant" selection but change the location to "US" or "Colorado" the market numbers will change. The Locations and Job Profiles both repeat in their respective columns (job profiles are repeated 51x - 1 for each state and 1 for national, and locations repeat 574x - once for each job profile). Here's how my data table is laid out:Data table.

As you can see, the market number changes based on location and job title. This is not the same as some of the other posts regarding matching two columns to return a 3rd because the selections from the first two columns are dynamic and always changing with a drop down list, not static like previous posts have. The drop-down lists are also not dependent.

I have tried nested VLOOKUP, INDEX, and nested XLOOKUP but always get a #N/A or #REF error. I created a "helper" column off to the side of the table to simplify the lookup criteria that has "Job_profile" in cell X2 (which is linked to the selection from the dropdown list on the dashboard), "Area_Title" in cell X3 (which is linked to the location drop down selection on the dashboard), and "Market" which is where I typed the formula below to return the number for that market and job profile. All the job titles are in Column E and all areas are in column B. Market totals are in column I. Here is the formula I have tried:

=VLOOKUP(X2,E2:E14200,VLOOKUP(X3,B2:B14200,9,FALSE))

I have tried the same formula above as an XLOOKUP but got another #REF error. I have also used this video: https://www.youtube.com/watch?v=S-yISEcBy9I but it didn't solve my problem because it depends on a 3rd criteria that is manually entered.

Seanf0912
  • 13
  • 4

2 Answers2

0

You could use a lookup variation like: =XLOOKUP(X2&"|"&X3,E$2:E$14200&"|"&B$2:B$14200,I$2:I$14200,"not found",0)

Or filter (which might be better understandable; each condition is wrapped in () and divided by * which means AND):

=FILTER(I$2:I$14200,(X2=E$2:E$14200)*(X3=B$2:B$14200),"not found")

P.b
  • 8,293
  • 2
  • 10
  • 25
  • Hi P.b., Thank you for the response. When I enter the Filter formula, it returns a #Calc! error. The XLOOKUP formula returns "Not Found" – Seanf0912 Jan 06 '23 at 20:27
  • In that case the combination of values you're looking for does not exist, or the values are misspelled (extra character). What values are X2 and X3? Would you be able to find your value when manually filtering on columns B and E for your dropdown values? – P.b Jan 06 '23 at 20:37
0

Here's the formula that worked. I had to use Index Match, where I indexed the table that contained the data I wanted to return (the market number) based on an exact match from two different cells (Job_Profile and AREA_TITLE).

=INDEX(Table13[Market], MATCH(1,('Predictive Funnel'!G4=Table13[Job_Profile])*('Predictive Funnel'!Q4=Table13[AREA_TITLE]),0))

Thank you @P.b for your help!

Fedor
  • 17,146
  • 13
  • 40
  • 131
Seanf0912
  • 13
  • 4