Returning data from a dropdown box using vlookup

**Paragoomba** · 06-27-2013, 10:42 AM

Ok cool. could you explain in the vlookup formula what the last few numbers are referring to?

**Tony Valko** · 06-27-2013, 10:53 AM

=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).

**Paragoomba** · 06-27-2013, 11:19 AM

alright its not working for the actual database ive been buildng so Im going to attach another sample workbook that is much more direct of the design i have. Would you be able to construct the formula to make it work in this sample? I would greatly appreciate it.

SampleWorkbook2.xlsx

**Tony Valko** · 06-27-2013, 11:35 AM

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?