VLOOKUP dynamic column reference

Question

Is it possible to use a cell reference containing a table header name to provide column number in a VLOOKUP match statement?

I have 2 dynamic dropdown lists, the first dropdown will identify manufacturer and populate the second dropdown with products, these 2 selections will then provide Table references from which to perform VLOOKUPS of items in column A.

$B$1 is a dropdown list of Data Sources 1,2 or 3

$B$2 is a dropdown list of Fields under each Data Source - all identical for this demonstration

Currently:

By using this formula construct the VLOOKUP works fine using the static Data Source 1 table reference in the MATCH statement to provide column number for the $B$2 Table heading.

=VLOOKUP($A4,INDIRECT($B$1),MATCH($B$2,Data_Source_1[#Headers],FALSE),FALSE)

Requirement:

I have many Tables to feed into this and want to make the Data_Source_X section dynamic by using the cell contents from the dropdown to denote which table to use for headings thus a column number for the VLOOKUP.

Is it possible to use a dynamic reference to calculate a column number in the MATCH statement?

Answer 1

a screenshot would help, its hard to imagine a table from a described cell reference.

Answer 2

Hi, screen shots uploaded, either this is possible and I just need to know how to format the MATCH reference to Data_Source_X to provide the column number from a Heading text or it is just not possible. If I select Source_Data other than the one hard coded for the column it will not find any data via the VLOOKUP, eg drop down for B offer me Source_Data_1, 2 or 3 and the correct Headings are listed but the VLOOKUP will fail until I can tell it to use the correct Column from the selected Table\Names range. This will be a report generator enabling the user to select any field from any data source.

=VLOOKUP($A4,INDIRECT($B$1),MATCH($B$2,Data_Source_1[#Headers],FALSE),FALSE)

Thanks for looking, I have generated a demo of this now to better explain the issue.

Dean

Answer 3

The formula in cell B1 should be:

="Data_Source_1!A"&1

The VLOOKUP formula:

=VLOOKUP(A4,OFFSET(INDIRECT($B$1),0,0,22,3),MATCH($B$2,OFFSET(INDIRECT($B$1),0,0,1,3),0),0)

File: DeanJHardingTest.xlsx

cheers

Answer 4

Thanks for your suggestion, I have now found a solution to my question by storing the column number in a hidden cell and referencing it in a VLOOKUP, so much time spent only to arrive at a really really simple solution.

Thanks

Dean

Answer 5

Good for you.