excel formula, finding column name from value, with constraint of a row name

Question

Hi Community,

I have an excel problem, i hope that you guys are able, to help me solve it.

Above i have a table with rows that are zones and columns that are pallets.

My question is, how do i write an formula, where the output is the amount of pallets, at a given value, in a specific row.

As you can see, in this example i have written it should look at zone 1 and value of 1300, to find the amount of pallets that match. In this case it would be 5, as i can see from looking at it.

But i want excel to do the job for me.

I have tried, with MATCH, VLOOKUP, IF and so on, but i can't seem to crack the code.

Is it even possible to this?

Please help, it would mean a lot to me :)

Answer 1

You can accomplish this by using a combination of MATCH, CHOOSEROWS, and INDEX functions.

Start by finding the row that contains the zone of interest with MATCH(D11,C4:C5,0). This searches column c and produces the relative row number that contains the desired zone, in this case 1.

Now find the column in that row that contains the specified value with MATCH(D12,CHOOSEROWS(D4:L5,MATCH(D11...)),0). This searches the row described above (1) and produces the relative column number that contains that value, in this case 5

Now find the number of pallets in the top row with INDEX(D3:L3,1,MATCH(D12...)). This selects the cell in the top row from the column identified above (5).

Putting it all together produces the formula =INDEX(D3:L3,1,MATCH(D12,CHOOSEROWS(D4:L5,MATCH(D11,C4:C5,0)),0))

If you prefer, you can break the formulas up. For example, if you put the formula =MATCH($D$11,C4:C5,0) in cell H11, then H11 contains the row of interest and the second match formula can be simplified to =MATCH($D$12,CHOOSEROWS(D4:L5,H11),0). If you put this formula in H12, then H12 contains the column of interest and the final formula gets simplified to =INDEX(D3:L3,1,H12).