How to use Table Nomenclature with the following Max function formula

[XCaliber]

Hi,

I need to use this formula, but use it within a Table that grows. In other words the array will become larger. I hope you can see what I am trying to do with this formula and I hope someone can show me how to recreate it for use in a table in stead of a Data set. Is there a way? If so, what is the Nomenclature to make this work?

MAX($B$11:B11)+1

 

[StephenCrump]

Now I can't use Vlookup

Sorry, it's not clear why ...

The VLOOKUP syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

For table_array, we are using TBL_Minor_Factions[[#Data],[Id]:[Minor Faction Name]] and for col_index_num, 2.
i.e. looking up on [Id] and taking the corresponding value in (relative) column 2, i.e. [Minor Faction Name]

As long as these two columns continue to adjoin each other, the VLOOKUP should work, regardless of these two columns' position in the table. If you have inserted intermediate columns, you'll need to adjust col_index_num.

If you're still stuck, can you please post your latest workbook so we can both be looking at the same version?

 

[XCaliber]

Here is the lastest file showing the ID Column Moved and still adjoining Minor factions: https://drive.google.com/open?id=0B3fREpgAxamJdTdfMThKTUItQW8

 

[XCaliber]

I am pretty sure that because I am using Vlookup, I need ID in the leftmost column regardless of Table nomenclature.

But, If you know otherwise I am willing to learn.

 

[XCaliber]

I can get it to work by adding a duplicate column, but I really don't want to do that if I don't have to.

Here's file #5 that has 2 columns Hidden. But everything works with Vlookup.

https://drive.google.com/open?id=0B3fREpgAxamJZDl0MmExVklnVHc

Is there a way to do this so the ID column can be anyway within the table and work?

I am trying to get away from using Vlookup whenever I can. A lot of folks say Index with Match is way more powerful than vlookup, but I have a lot to learn to use them together.

 

[StephenCrump]

Ahh! You've reversed the order of the columns.

That's why the VLOOKUP isn't working, and why it does work if you insert a copy of the [Minor Faction Name] column to the right of the [Id] column.

INDEX/MATCH doesn't have this limitation. In the [Srch Results] column, replace:

=IFERROR(VLOOKUP(ROWS(INDEX([Srch Results],1):[@[Srch Results]]),TBL_Minor_Factions3[[Id]:[Minor Faction Name2]],2,0),"")

with:

=IFERROR(INDEX([Minor Faction Name],MATCH(ROWS(INDEX([Srch Results],1):[@[Srch Results]]),[Id],)),"")

Now you should be able to delete the [Minor Faction Name2] copy column, and rearrange the column order any way you want?

 

[XCaliber]

Yes Sir! That worked !!

 

[XCaliber]

I have got to learn how to use index with Match that is so awesome! Thank you so much!

 

[StephenCrump]

You're welcome.