General question regarding tables

[thewiseguy]

I have a table consisting of roughly 20 columns. In a worksheet i have a formula which contains a VLOOKUP. My table array within my VLOOKUP is the same as my aforementioned table, however when I add a column to my table the VLOOKUP formula no longer works. Isn't this what creating a table is supposed to achieve? I thought creating the table would allow me to create and add columns without it affecting my formulas?

 

[thewiseguy]

..........

 

[Aladin Akyurek]

What is the formula that ceases to work after adding in an extra column?

 

[thewiseguy]

=IF(ISNA(VLOOKUP(G6,WHOLE,19,FALSE)),"0",VLOOKUP(G6,WHOLE,19,FALSE))


when I add a column in the table the formula above needs to be changed to:

=IF(ISNA(VLOOKUP(G6,WHOLE,20,FALSE)),"0",VLOOKUP(G6,WHOLE,20,FALSE))

 

[Aladin Akyurek]

ADVERTISEMENT

=IF(ISNA(VLOOKUP(G6,WHOLE,19,FALSE)),"0",VLOOKUP(G6,WHOLE,19,FALSE))


when I add a column in the table the formula above needs to be changed to:

=IF(ISNA(VLOOKUP(G6,WHOLE,20,FALSE)),"0",VLOOKUP(G6,WHOLE,20,FALSE))

What is the range WHOLE refers to, and are you adding the extara column within the range WHOLE represents?

 

[thewiseguy]

What is the range WHOLE refers to, and are you adding the extara column within the range WHOLE represents?

WHOLE = A4:Z56

the range WHOLE is also the same range as the table I created. Yes I am adding the column to this range.

 

[Aladin Akyurek]

ADVERTISEMENT

WHOLE = A4:Z56

the range WHOLE is also the same range as the table I created. Yes I am adding the column to this range.

Try...

=IF(ISNUMBER(MATCH(G6,Data!$A$4:$A$56,0)),INDEX(Data!$S$4:$S$56,MATCH(G6,Data!$A$4:$A$56,0)),0)

instead of:

=IF(ISNA(VLOOKUP(G6,WHOLE,19,FALSE)),"0",VLOOKUP(G6,WHOLE,19,FALSE))

 

[thewiseguy]

Try...

=IF(ISNUMBER(MATCH(G6,Data!$A$4:$A$56,0)),INDEX(Data!$S$4:$S$56,MATCH(G6,Data!$A$4:$A$56,0)),0)

instead of:

=IF(ISNA(VLOOKUP(G6,WHOLE,19,FALSE)),"0",VLOOKUP(G6,WHOLE,19,FALSE))

thanks, I will try it. does this mean i can completely forget about WHOLE and just use the INDEX function on any other situations such as this?

 

[Aladin Akyurek]

thanks, I will try it. does this mean i can completely forget about WHOLE and just use the INDEX function on any other situations such as this?

The Index/Match pair is versatile, and a bit faster than VLOOKUP in situations where both apply.

That said, you can use WHOLE also with Index/Match, but the formula will become a bit complicated:

=IF(ISNUMBER(MATCH(G6,INDEX(WHOLE,0,1),0)),
    INDEX(WHOLE,MATCH(G6,INDEX(WHOLE,0,1),0),H6),0)

where H6 houses a relevant column number like in VLOOKUP, e.g, 19.

 

[thewiseguy]

The Index/Match pair is versatile, and a bit faster than VLOOKUP in situations where both apply.

That said, you can use WHOLE also with Index/Match, but the formula will become a bit complicated:

=IF(ISNUMBER(MATCH(G6,INDEX(WHOLE,0,1),0)),
    INDEX(WHOLE,MATCH(G6,INDEX(WHOLE,0,1),0),H6),0)

where H6 houses a relevant column number like in VLOOKUP, e.g, 19.

Thanks Aladin. This will help me tremendously. My worksheet is constantly changing so this will really cut down on the amount of re-formatting I need to do.

Much appreciated!!

ps - it worked.