vlookup using table references

[Burnsieboy]

Morning all,

I am trying to make my sheet more robust by changing my usual vlookup
=IF($B9>0,VLOOKUP($B9,Full_list_Data,2,FALSE),"")

to something more like
=IF($B9>0,VLOOKUP($B9,Table1[Project Name],Table1,0),0)

the "Full_List_Data" or "Table1" data table is on another sheet and the table is B:CZ

I want to be able to rearrange the columns in the data table without affecting the existing column lookup numbers in the formulas on the other worksheets - there are lots

 

[pgc01]

Hi

If I understand correctly you want to find the $B9 value in the first column of Table1 and return the corresponding value in the [Project Name] column.
Is this correct?
If this is correct seems it's much simpler with index(match()).
Please confirm.

 

[Burnsieboy]

Spot on.... without the loose column ref in the standard vlookup

Chris

 

[pgc01]

In that case I'd use:

=INDEX(Table1[Project Name],MATCH($B8,INDEX(Table1,0,1),0))

 

[Burnsieboy]

Thanks, maybe one day I'll get my head around this..

Exactly what I needed

 

[pgc01]

You're welcome. Thanks for the feedback.