CODEWITHSUNDEEP
excel
Vincent De Smet
Vincent De Smet

Reputation: 4979

Using a column reference for a vlookup formula

When inserting a table in Excel you can refer to:

which makes formulas very easy to read: =COUNTIF(A:A,A1) becomes

=COUNTIF(Suppliers[IBAN],Suppliers[@[IBAN]])

to quickly check for duplicates (IBAN) of the current row value.

I'm now trying to convert =VLOOKUP([@SupplierCode],Suppliers[Code],2,False) into

=VLOOKUP([@SupplierCode],Suppliers[Code],Suppliers[Name],False)

So in other words, a way to get the column index of a column within a tableObject to use it in a formula (i.e. vlookup).

I could probably write a VBA function that does this, but I'm trying to find out if Excel has a way to handle this without VBA code

The VBA solution I do not want (but might settle for in case it is not possible):

Public Function GetColumnIndex(TableName As String, ColumName As String) As Long
    GetColumnIndex = ActiveSheet.ListObjects(TableName).ListColumns(ColumName).Index
End Function

allowing me to write: =VLOOKUP([@SupplierCode],Suppliers,GetColumnIndex("suppliers","Name"),FALSE)

works, as long as the tables are on the same sheet

Upvotes: 3

Views: 14925

Answers (4)

robertocm
robertocm

Reputation: 132

Working with tables, I think a better option is to replace the VLOOKUP formula with the INDEX formula in this way:

=INDEX(MyTableName[NameOfFieldContainingReturnValue],MATCH(LookupValue,MyTableName[NameOfFieldContainingLookupValue],0))

Some comments:

  • Index function can reference to a simple column in this way: =INDEX(MyTableName[FieldName];4)

  • This would return the value in 4th row in the column 'FieldName'

  • As we don't know the row number, we can use MATCH in the column containing the lookup value (this would be the most left column in the case of using vlookup formula).

Upvotes: 2

Vincent Gheur
Vincent Gheur

Reputation: 51

The answer may be generalized to cases where the lookup value is not in the first column:

=VLOOKUP("g";Table[[LookupColumn]:[ValueColumn]];MATCH("ValueColumn";Table[[#Headers];[LookupColumn]:[ValueColumn]];0);FALSE)

like in the following example:

enter image description here

Upvotes: 0

Dave
Dave

Reputation: 1643

Now added as answer: =MATCH("Name",Suppliers[#Headers],0) gives the index of the header matching the name so

The only way I can think to do it without VBA is to use the Match() function

=VLOOKUP([@SupplierCode],Suppliers,MATCH("Name",Suppliers[#Headers],0),FALSE)

Upvotes: 4

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

I've found this link invaluable Using structured references with Excel tables

I created a little Suppliers table with 2 columns, SupplierCode and Name then added a couple of entries below each heading.

When I pasted your =VLOOKUP(@SupplierCode,Suppliers[Code],2,False)

Into a a cell to the right of the 1st row of my table I was confronted with a message I've never seen before:

Excel alert

and when closed the @Supplier part of your formula was highlighted. Changing that to Suppliers[@SupplierCode] fixed that problem.

Assuming SupplierCode is in the first column of your table you can find the lookup column using the MATCH function within your VLOOKUP function as you have already discovered.

Upvotes: 1

Related Questions