Prioritising an index match lookup function

[wtau312]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Last company in row[/TD]
[TD="align: center"]Apple[/TD]
[TD="align: center"]Microsoft[/TD]
[TD="align: center"]Facebook[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Facebook[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Microsoft[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]x[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Last company in row[/TD]
[TD="align: center"]Apple[/TD]
[TD="align: center"]Microsoft[/TD]
[TD="align: center"]Facebook[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Facebook[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]x[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Apple[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]x[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[Aladin Akyurek]

Hard to follow... Are you asking to return the company to which the last numerical value corresponds even when there is a text value associated with a different company?

 

[wtau312]

Yes, that is exactly right!

 

[wtau312]

So the priority would be: 1. return the company to which the last numerical value corresponds and 2. if there is no company with a numerical value, then return the company to which the last text value corresponds

 

[Aladin Akyurek]

In A2 enter and copy down:

=LOOKUP(REPT("z",255),CHOOSE({1,2},LOOKUP(REPT("z",255),B2:D2,$B$1:$D$1),LOOKUP(9.99E+307,B2:D2,$B$1:$D$1)))

 

[wtau312]

Thank you so much!

 

[Aladin Akyurek]

Thank you so much!

You are welcome.