Returning data from a dropdown box using vlookup

If the fields are the same for each job then you can redesign your table to look like this...

J = Job
F = Field
P = Person

......F1.....F2.....F3
J1...P1.....P2.....P3
J2...P4.....P5.....P6
J3...P7.....P8.....P9

I redefined all the named ranges and this is the resulting formula:

=VLOOKUP(A1,JobTable,MATCH(A5,Fields,0)+1,0)

Here's your file with this new design.

SampleWorkbook(1).xlsx

=VLOOKUP(A1,JobTable,MATCH(A5,Fields,0)+1,0)

MATCH(A5,Fields,0)

The 0 means to find an "exact" match of cell A5 (case is not a factor).

MATCH(A5,Fields,0)+1

The +1 is an "offset correction". The MATCH function will return a number from 1 to 3 but we need these numbers to be from 2 to 4 (the relative column numbers of the table).

=VLOOKUP(A1,JobTable,MATCH(A5,Fields,0)+1,0)

The 0 means to find an "exact" match of cell A1 (case is not a factor).

One thing you have to correct is the source for the drop down in cell A5.

Excel won't allow you to use the names from the drop down in cell A2 because they are also valid cell references. For example, Job1 is a valid cell address (column JOB, row 1). When this happens Excel concatenates an underscore to the string:

_Job1

This tells Excel that the string is not a cell reference.

So, for the drop down list source in cell A5 use:

=INDIRECT("_"&A2)

Ok, so what result are you looking for? Do you want to return the column headers based on the selections of the drop down lists?

If I select Job2 in A2 and Person2 in A5 what result do you expect?

OK, here's the formula using cell addresses instead of named ranges.

=VLOOKUP(A2,ControlInterface!A:J,MATCH(A5,ControlInterface!A1:J1,0),0)

You're welcome. Thanks for the feedback!