excel - Create a usable reference for a whole column using a formula

Question

I have a spreadsheet where I need a reference to a whole column, but the location on the column is not determined ahead of time, so I used INDEX MATCH, ADDRESS, and CELL functions to find that out. Once I find the right column I need to reference cells in it.

Two problems stop me:

• I have columns that have words or letters as data, and columns with numbers as data.
• I need to reference whole column simply, because the amount of rows is varying as well.
• The formula I tried doesn't work, it just lets me view the location.

I tried using the below formula, and got the output I wanted, but it merely just displays the address, and when I place it in a formula, it doesn't work. I needed something that I could use as a reference in a formula.

Formula for State

=LEFT(ADDRESS(7,CELL("col",INDEX('Extracted Sales Data (2)'!B7:AI200000,1,MATCH("*"&"Ship"&"*"&"State"&"*",'Extracted Sales Data (2)'!B7:AI7,0))),4,1,"Extracted Sales Data"),28)&":"&LEFT(ADDRESS(7,CELL("col",INDEX('Extracted Sales Data (2)'!B7:AI200000,1,MATCH("*"&"Ship"&"*"&"State"&"*",'Extracted Sales Data (2)'!B7:AI7,0))),4,1),1)

Formula for Revenue Amount

=LEFT(ADDRESS(7,CELL("col",INDEX('Extracted Sales Data (2)'!B7:AI200000,1,MATCH("*"&"Rev"&"*"&"Am"&"*",'Extracted Sales Data (2)'!B7:AI7,0))),4,1,"Extracted Sales Data"),28)&":"&LEFT(ADDRESS(7,CELL("col",INDEX('Extracted Sales Data (2)'!B7:AI200000,1,MATCH("*"&"Rev"&"*"&"Am"&"*",'Extracted Sales Data (2)'!B7:AI7,0))),4,1),1)

The above formula returns the below values, but I can't use it as a reference.(Note that the columns are not pre-determined)

State Column

'Extracted Sales Data'!G:G

Revenue Amount column:

'Extracted Sales Data'!AQ:AQ

I'm trying to use it in SUMIF and COUNTIF functions.

=SUMIF('Extracted Sales Data'!G:G,M16,'Extracted Sales Data'!AQ:AQ)

or

=COUNTIF('Extracted Sales Data'!G:G,M16)

Sample Data:

One type of data set:

Ship-to-State          Trx Type     RC Revenue Amount
Alabama                INVOICE      50000 
California             INVOICE      3000

Another type of data set

Customer          Ship to State          Trx Type     Expense Amount     Revenue Amount
Customer A        Alabama                INVOICE      21000              50000 
Customer B        California             INVOICE      1680               3000

I need to have one formula for both data sets, how do I achieve this? (Reference whole column based on a lookup/match.)

question from:https://stackoverflow.com/questions/66057396/create-a-usable-reference-for-a-whole-column-using-a-formula

Answer 1

You only need the INDEX/MATCH parts:

INDEX('Extracted Sales Data (2)'!B7:AI200000,0,MATCH("*"&"Rev"&"*"&"Am"&"*",'Extracted Sales Data (2)'!B7:AI7,0))

With the 0 in the second criterion it will return the full column.

So then you would just use those in the formulas:

=SUMIF(INDEX('Extracted Sales Data (2)'!B7:AI200000,0,MATCH("*"&"Ship"&"*"&"State"&"*",'Extracted Sales Data (2)'!B7:AI7,0)),M16,INDEX('Extracted Sales Data (2)'!B7:AI200000,0,MATCH("*"&"Rev"&"*"&"Am"&"*",'Extracted Sales Data (2)'!B7:AI7,0)))

And do the same with the COUNTIF