vlookup on several columns

[ddesantis]

Hi,
I have a unique identifier and I need to look for it across multiple tables; all these tables are in a single tab.
Once I find this ID I always need to get the +2 column number

I tried
- vlookup, but the ID has always to be on the first column, and this is not the case
- Index and match but I've to generate multiple index and match and specify exactly the columns where the possible result is.
Hope I've been clear, please let me know if you can help
Thanks
Daniele

 

[ghrain22]

Are the column headers consistantly named? you could use that as the premise to grab the appropriate column to lookup against.

 

[ddesantis]

yes, columns across tables are consistently named

 

[ghrain22]

yea than you can use a match function to target that column in a index match:

Index(DataArray,Match(ID, RowLabelArray,0),Match("ColumnHeader", ColumnLabelArray,0))

You could do a similar method with an lookup/index/match

 

[ddesantis]

I don't know where to start the "RowLabelArray" and the "ColumnLabelArray" from

- RowLabelArray: is from the first row where I can find the ID to the last? let's say I5:I100
- ColumnLabelArray:is from the first column where I can find the ID to the last? let's say I5:Z5

 

[ddesantis]

[TABLE="width: 500"]
<tbody>[TR]
[TD]entity 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]entity 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]entity 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]vlookupvalue[/TD]
[TD]parameter[/TD]
[TD][/TD]
[TD]vlookupvalue[/TD]
[TD]parameter[/TD]
[TD][/TD]
[TD]vlookupvalue[/TD]
[TD]parameter[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]entity1USD[/TD]
[TD]30%[/TD]
[TD][/TD]
[TD]entity2USD[/TD]
[TD]40%[/TD]
[TD][/TD]
[TD]entity3USD[/TD]
[TD]50%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I made it works; but it seems only looking at one column only

the one above is sheet1

In sheet 2 I have the follows
- entity2USD
- entity3USD
and In sheet 2 I need to associate these with the relative parameter, such us
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]entity2USD[/TD]
[TD]40%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]entity3USD[/TD]
[TD]50%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[ghrain22]

I don't know where to start the "RowLabelArray" and the "ColumnLabelArray" from

- RowLabelArray: is from the first row where I can find the ID to the last? let's say I5:I100
- ColumnLabelArray:is from the first column where I can find the ID to the last? let's say I5:Z5

RowLabelArray would be your ID's you're looking up.
ColumnLabelArray would be your column headers.

 

[ddesantis]

Thanks,
but I cannot still work out how to make it work on different columns. See the example above

 

[ghrain22]

Thanks,
but I cannot still work out how to make it work on different columns. See the example above

Not sure if i'm following your sample.
Some things to consider:
-It appears that you have multiple column headers; consider consolidating them and using a similar match function as above.
-Alternatively, create named ranges for each entity and use an index/indirect/match

 

[ddesantis]

Each entity is a table made of 2 columns vlookup and parameter. Vlookup and Parameter are column headers consistent across all tables

How canI create named ranges?