arraysexcelexcel-formulaxlookup

I want the result of a function to return the value at the top of the column in an array instead of the position of the cell it's searching for

I have the following function: =ADDRESS(SUMPRODUCT((P29:AD37=T11)*ROW(P28:AD28)),SUMPRODUCT((P29:AD37=T11)*COLUMN(P28:AD28))) which returns the position of the value it's searching for. I would like it to instead return the value listed at the top of the column in an array, essentially a label, in order to accurately label what part of the array its in.

The above function references another cell (T11) that spits out a value. The referenced cell's function is just a basic division calculation rounded up.

I don't mind further complicating this function and I don't mind having to reference this function's cell in another cell if need be. I'm also fully aware I may be overthinking this so if that's the case I'm happy to take my licks.

For context, I'm looking to have a handy in-sheet Pallet Classification Calculator for logistics invoicing. Given the number of programs and webpages I need up while working, I want to have this at hand on a sheet I'm already using for calculating cost.

I am not extremely well versed in excel so if there is a lack of clarity here I will do my best to explain the issue better as answers come in.

So far I've tried various combinations of XLOOKUP, INDEX, MATCH, and a combination of them all with little luck. The provided formula has been the closest I've been able to get to giving me the information I need. It's indeed spitting out the correct class identification but it's only giving me it's position and not the actual contents of the cell itself.

For reference, this is the information I'm working off of:

enter image description here

Thank you!

Solution

  • You could put the data into a single column and then use xmatch, which gives you four options:

    (1) First match or next smaller item from start

    =LET(headers,P28:AD28,
 table,P29:AD37,
 INDEX(headers,QUOTIENT(XMATCH(T11,TOCOL(table,0,TRUE),-1,1)-1,ROWS(table))+1))

    (2) First match or next larger item from start

    =LET(headers,P28:AD28,
 table,P29:AD37,
 INDEX(headers,QUOTIENT(XMATCH(T11,TOCOL(table,0,TRUE),1,1)-1,ROWS(table))+1))

    (3) First match or next smaller item from end

    =LET(headers,P28:AD28,
 table,P29:AD37,
 INDEX(headers,QUOTIENT(XMATCH(T11,TOCOL(table,0,TRUE),-1,-1)-1,ROWS(table))+1))

    (4) First match or next larger item from end

    =LET(headers,P28:AD28,
 table,P29:AD37,
 INDEX(headers,QUOTIENT(XMATCH(T11,TOCOL(table,0,TRUE),1,-1)-1,ROWS(table))+1))

    For example if you put in 1.5 you would get

    Value Result Description
    1.5 Class 400 First match or next smaller item from start
    Class 300 First match or next larger item from start
    Class 500 First match or next smaller item from end
    Class 400 First match or next larger item from end
    Class 65 Class 70 Class 77.5 Class 85 Class 92.5 Class 100 Class 110 Class 125 Class 150 Class 175 Class 200 Class 250 Class 300 Class 400 Class 500
    22.5 15 13.5 12 10.5 9 8 7 6 5 4 3 2 1 1
    23 16 14 13 10 10 8.5 7.5 7 6 5 4 3 2 1
    24 17 15 13.5 11 10.5 9 8 7 6 5 4 3 2 1
    25 18 15 13.5 12 10.5 9 8 7 6 5 4 3 2 1
    26 19 15 13.5 12 10.5 9 8 7 6 5 4 3 2 1
    27 20 15 13.5 12 10.5 9 8 7 6 5 4 3 2 1
    28 21 15 13.5 12 10.5 9 8 7 6 5 4 3 2 1
    29 22 15 13.5 12 10.5 9 8 7 6 5 4 3 2 1
    30 22.5 15 13.5 12 10.5 9 8 7 6 5 4 3 2 1