General question regarding tables

thewiseguy

Well-known Member
Joined
May 23, 2005
Messages
954
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
=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))
 
Upvote 0
=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?
 
Upvote 0
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.
 
Upvote 0
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))
 
Upvote 0
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?
 
Upvote 0
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:
Code:
=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.
 
Upvote 0
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:
Code:
=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.
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top