Hello all,
This is my first post on this forum, so let me know I haven't described my problem well enough.
I have an index match lookup function which returns the position of the last cell in a row containing a non-numerical value, to thus match the row number with the corresponding company in row 1. The function works like this:
<tbody>
</tbody>
For A2, the formula would look like this: =INDEX($B$1:$D$1,MATCH(LOOKUP(REPT("z",255),B2:D2),B2:D2,0)).
What I would like to be able to do instead is to sometimes give priority to a particular company, such that if even though there's a cell afterwards containing a non-numerical value, the formula still returns the row position of the prioritised company.
In order to do this, my best idea so far is to instead assign a numerical value to the prioritised companies, and then run the same index match lookup formula that instead returns the row position of the last cell containing a numerical value. For A2 it would look like: =INDEX($B$1:$D$1,MATCH(LOOKUP(9.99E+307,B2:D2),B2:D2,0)).
I would then prioritise it with an if-function that counts the number of cells in the row containing a numerical value, and if that number is equal to or above 1, then it would run the numerical index match lookup function, and if not, it would run the non-numerical index match lookup function. Ideally, it would look like this:
<tbody>
</tbody>
I can't seem to get the syntax right however. It seems like the numerical index match lookup function ends up returning nothing, if there's a cell containing a non-numerical value after the last cell containing a numerical value.
Do any of you have an idea how I might fix this?
Any help would be greatly appreciated!
This is my first post on this forum, so let me know I haven't described my problem well enough.
I have an index match lookup function which returns the position of the last cell in a row containing a non-numerical value, to thus match the row number with the corresponding company in row 1. The function works like this:
| A | B | C | D | |
| 1 | Last company in row | Apple | Microsoft | |
| 2 | x | |||
| 3 | Microsoft | x | x |
<tbody>
</tbody>
For A2, the formula would look like this: =INDEX($B$1:$D$1,MATCH(LOOKUP(REPT("z",255),B2:D2),B2:D2,0)).
What I would like to be able to do instead is to sometimes give priority to a particular company, such that if even though there's a cell afterwards containing a non-numerical value, the formula still returns the row position of the prioritised company.
In order to do this, my best idea so far is to instead assign a numerical value to the prioritised companies, and then run the same index match lookup formula that instead returns the row position of the last cell containing a numerical value. For A2 it would look like: =INDEX($B$1:$D$1,MATCH(LOOKUP(9.99E+307,B2:D2),B2:D2,0)).
I would then prioritise it with an if-function that counts the number of cells in the row containing a numerical value, and if that number is equal to or above 1, then it would run the numerical index match lookup function, and if not, it would run the non-numerical index match lookup function. Ideally, it would look like this:
| A | B | C | D | |
| 1 | Last company in row | Apple | Microsoft | |
| 2 | x | |||
| 3 | Apple | 1 | x |
<tbody>
</tbody>
I can't seem to get the syntax right however. It seems like the numerical index match lookup function ends up returning nothing, if there's a cell containing a non-numerical value after the last cell containing a numerical value.
Do any of you have an idea how I might fix this?
Any help would be greatly appreciated!
