Using Tables in Lookup Formulas

[scecchin]

Hi


Having a bit of a problem finding something that is already written, so here is my question. I have a table (the new functionality) and I am using that to generate a validation list. I would then like to select related data from the same table, based on the selection via the validation list.


As an example, I have a three column table. Columns are Employee, Office, Floor. Using a validation list I select the employee. I would then like to have a lookup of some sort, in the adjoining column, that shows the office, or floor.


Thanks

Sergio

 

[Luke M]

Check out the VLOOKUP function. Your formula will look something like:

=VLOOKUP(Employee,MyTable,2,FALSE)

and

=VLOOKUP(Employee,MyTable,3,FALSE)


for the Office and Floor respectively.

 

[scecchin]

Hi Luke


Thanks, that was embarrassingly simple.;-)


It works and I have a follow up question. Is there a way to use the table column name as a reference instead of the 2 or 3? I ask because it would potentially be a way to have some clarity in a larger, complicated spreadsheet.

 

[oldchippy]

Hi,


Take a look here


http://office.microsoft.com/en-gb/excel-help/dynamic-searching-using-vlookup-match-and-index-HA001154902.aspx

 

[SirJB7]

Hi, Sergio!


With a table at Sheet1!A1:J101 with titles in 1st row and 100 data rows across 10 columns, try this at any off table place:

=VLOOKUP(Sheet2!G3,Sheet1!$A$2:$J$101,HLOOKUP("Col title",Sheet1!$A$1:$J$1,1,FALSE),FALSE)


If you have named ranges as:

TableTitles: =$A$1:$J$1

TableData: =$A$2:$J$101

you can do this:

=VLOOKUP(Sheet2!G3,TableData,MATCH("Col title",TableTitles,0),FALSE)


And the same formula may be used if you have dynamic named ranges as follows (with the advantage that you could expand down and to the right your table without modifying any formula), but with the condition that the table is the only data in its 1st row and 1st column, i.e., nothing else should be in row 1 nor in column 1:

TableTitles: =OFFSET($A$1,0,0,1,COUNTA($1:$1))

TableData: =OFFSET($A$2,0,0,COUNTA($A:$A)-1,COUNTA($1:$1))


Just advise if an issue.


Regards!


EDITED: Changed HLOOKUP by MATCH.

 

[scecchin]

Thanks guys, but let me clarify what I am hoping to do.


I created a table with the new (from Excel 2007) tables function (which replaces lists). The table name of the table I created was, (for example), << tblLocation >> and it has 3 columns; Employee, Office, Floor.


If I write a formula in a cell not in, but adjacent to the table, I can write the following formula, << =tblLocation[Employee] >>, it will bring me the name in the Employees column of the same row. If I write another formula << =tblLocation[Office] >>, I will get the value from the Office column.


In the lookup, (VLOOKUP or otherwise), I would like to use this name, [Employee] or [Office], instead of 1 or 2 to reference the column.


The table name worked per Luke M's suggestion, but for column reference he was using the 2 or 3.


I can use the INDEX/MATCH option instead of VLOOKUP if that could work.


Thanks

Sergio

 

[Luke M]

I think this is the closest you can get:

=VLOOKUP($A2,MyTable,MATCH(B$1,MyTable[#Headers],0),0)


Where:

MyTable is your lookup table

A2 is the employee name you want to lookup

B1 (and presumably C1) contain the names of the columns you want to return from MyTable

 

[SirJB7]

Hi, Sergio!

Complementing my previous suggestion, if you have a table you can dispense with both named ranges and do this:

=VLOOKUP(Sheet2!G3,TableData[#Data],MATCH("Col title",TableData[#Headers],0),FALSE)

I guess that in English it'd be Headers, if not just adjust it.

Regards!

 

[SirJB7]

@Luke M

Hi!

It seems as if I left this screen half an hour ago, doesn'it?

Regards!

 

[scecchin]

Hi


Okay, first of all, thank you! With your suggestions, I was able to achieve what I was after. My formula ended up looking like this:


=VLOOKUP($E$1,tbl_INFO,MATCH(tbl_INFO[[#Headers],[Office]],tbl_INFO[#Headers],0),FALSE)


This is what I was after. The benefits here are many, namely that moving columns within the table, or even renaming the column heading, does not corrupt any formulas referencing the table.


I am starting to really like this table functionality!


Sergio

 

[SirJB7]

Hi, Sergio!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!