Vlookup table (not range)

[Dancarro]

Hi,

I am trying to do a vlookup with a table (don't get confused with the range on).

So I have two tables (not range) and I am trying to lookup a value in table2 but for some reason it doesn't work like range lookup.

Could someone help know how to reference the table in the vlookup and to specify the column.

Many thanks,
Dan

 

[Gerald Higgins]

Can you post the formula you have now please ?
And in what way exactly is it not doing what you want ?

 

[Dancarro]

Hi,

The formula i am using is the normal vlookup formula:

=Vlookup([@[EmployeeID]],Table1[[#All]],Table1[Sales],FALSE)

The problem I encounter is to specify the column within Table 1

 

[Norie]

You don't specify the column, you specify the position of the column within the range.

What you could do in this case is use MATCH to find the position of the 'Sales' column in Table1.

=Vlookup([@[EmployeeID]],Table1[[#All]],MATCH("Sales",Table1[#Headers], 0), 0)

 

[Dancarro]

Hi Norie,

I tried your formula. It does give the column but the result is N/A as it doesn't match the employee ID

 

[Norie]

Dan

It works fine for me, have you checked there's actually a match for the EmployeedID you are looking up?

 

[Dancarro]

Hi Norie,

The issue is table1, where the data needs to be from the second column and not the first column.

How do I change the table array to select the columns?

 

[RoryA]

I'd suggest Index/Match at this point:

=INDEX(Table1[Sales],MATCH([@[EmployeeID]],Table1[Column 2 name here],0))

Change the blue part appropriately

 

[Dancarro]

Hi Rory,

Worked perfectly.

Many thanks