Referencing all non-blank values in a row/column

Occasional Contributor

Hello Microsoft Community,

 

I've been building up a large worksheet that has several sheets using functions such as XLOOKUP & VLOOKUP from a series of tables. I expect to use this sheet for several months and constantly add more data points to the tables. I'm running into issues though, where, if I add data to the tables I have to manually go over every sheet and update the lookup formula's range.

 

In Google Sheets you can reference an entire column/row within a formula and it will return the array of all non-blank cells. Is this possible in Excel 365?

If not, the other solution I was thinking of was using INDIRECT everywhere and reference the last row/column, but I fear this might be expensive and bulky.

 

Any other methods in which I could optimize the worksheet?

 

All ideas are welcome

 

Thanks!

7 Replies

@ElRafaVaz33 

That is Excel 2003-thinking.

Since Excel 2007 you can use Excel tables and structured references.

 

Hey Detlef, thanks for the answer.

The issue with Tables is that they don't allow to have formulas inside of them; I always get #SPILL errors if I try to change my data to Tables

@ElRafaVaz33 

Depends on how you build the formula. In structured tables that is per-row reference, formula is automatically populated on entire table.

Hey Sergei, not sure I understand; what do you mean by a structured table with a per-row reference? when I've tried mixing tables and references I've always gotten a spill error; is this a different type of table?

@ElRafaVaz33 

I mean the difference between these two formulae

image.png

Not sure which one do you use.

I see! I always attempted the first instance, let me try the second and see if it works.

Thanks Sergei!

@ElRafaVaz33 , you are welcome