dynamic data in table and index, vlookup, match help

[bntwillis]

Hi All,
First post here.
I have a number of tables that get populated dynamically via xml maps on a scheduled basis.
I then take this data and populate various other worksheet tabs in my spreadsheet. I use vlookup for most of this other repopulating of data. But with one of my tables I cannot use vlookup because the first row does not have the information I can key on.
So I tried using the information provided in the excellent article found here
http://www.mrexcel.com/articles/excel-vlookup-index-match.php
But I am not successful.
I think the issue involves defining my cell ranges.
I cannot use something like $A$1:$C$45 as susggested in the article because my tables are dynamic and change in size from month to month.
So I tried the formula like this"
=INDEX(TableName,MATCH(VALUETOKEYON,FALSE),3)
Where TableName is the name of my dynamically created table, VALUETOKEYON is the unique value to key on and 3 is the data column I want passed to the new cell.
But I get an Invalid Name Error when using this. I assume it is because INDEX does not know how to use a TableName like VLOOKUP does, is that correct?

If so, does anyone know how to get around this?
Thanks,
Bob

 

[jbeaucaire]

That's not how index/match works. Here's a simple example for you to "convert" to your use:

=INDEX(A1:A100,MATCH(C3,B1:B100,0))

The INDEX should be for a single column/row of data. You CAN use a named range like in your sample formula, just make sure it's a single row/column.

The MATCH should include a ValueToKeyOn like you suggested, C3 in my formula. Then the next parameter is another range of data to match that value against. The range needs to have the ValueToKeyOn in it somewhere, and the range needs to be the same height or width as the range you INDEXed.

The final parameter is zero, which means "find an exact match".