CODEWITHSUNDEEP
excel
Matthias Pospiech
Matthias Pospiech

Reputation: 3494

excel vlookup in tables - search for row and report value in column using column name

I have a database table that is structured like this

SAP Number, FOW, WD, DG

In a different sheet I am using data from this list It looks like this

SAP Number, WD, DG, ....

The SAP Number is given, WD and DG are searched using VLOOKUP

=VLOOKUP($A2,Objektive!$B:$M,10,FALSE)

This however is difficult to read and could be solved much easier. I know that the search value is in $A2, and I know that the value is in a tabular named "WD". Is it possible within Excel to search for the Row similar ot VLOOKUP, but use the column using the column name as [@[WD]]

Here some peudo-code

=VLOOKUPTABLE($A2,Data[@[WD]],FALSE)

Upvotes: 1

Views: 560

Answers (2)

Wedge
Wedge

Reputation: 1826

If your data table starts from the first column, you can use the COLUMN function to determine the index of the column you want to return data from in the table using the name of that column.

=VLOOKUP($A2,Data,COLUMN(Data[WD]),FALSE)

Alternatively you can use Index/Match to directly reference the columns you want to match and return data from.

=INDEX(Data[WD], MATCH($A2, Data[SAP Number], 0))

Upvotes: 1

Marcel
Marcel

Reputation: 1463

If you create a table from the Excel (select any of the cells and press Ctrl + T) you gain access to named references which you can use in Excel formulas. Just make sure your data contains headers.

Upvotes: 0

Related Questions