Anything faster than =LOOKUP(2,1.... ?

[JumboCactuar]

Hi,

i use these formulas quite a lot for looking up previous match

is there a more efficient way? As these seem to slow everything down

Formulas below are based on text data in column A, and values in Column B

​Lookup Column C

=LOOKUP(2,1/($A$1:A2=A2),$B$1:B2)

Someone on Reddit gave me this alternative

Index Column D

=IFERROR(INDEX(B$1:B1,1/(1/MAX(INDEX((A$1:A1=A2)*ROW(A$1:A1),)))),"")

Source: https://www.reddit.com/r/excel/comments/ak3r8k/anything_faster_than_lookup21/

Both produce similar speeds, is this realistically the fastest I'm gonna get with formulas?

Can anyone come up with anything quicker?

 

[Aladin Akyurek]

Not a good idea invoking 1/(1/… concept as it is not always 1/(1/X) = X in the real world.

I think it's the sheer number of formulas that is the trouble. By the way, =LOOKUP(2,1/($A$1:A2=A2),$B$1:B2) returns last match.

 

[JumboCactuar]

Not a good idea invoking 1/(1/… concept as it is not always 1/(1/X) = X in the real world.

I think it's the sheer number of formulas that is the trouble. By the way, =LOOKUP(2,1/($A$1:A2=A2),$B$1:B2) returns last match.

Ye it's meant to be

=LOOKUP(2,1/($A$1:A1=A2),$B$1:B1)

 

[Aladin Akyurek]

Ye it's meant to be

=LOOKUP(2,1/($A$1:A1=A2),$B$1:B1)

Are you entering it in C1 or C2?

 

[JumboCactuar]

Are you entering it in C1 or C2?

in C2, works as intended

results

 

[Aladin Akyurek]

Let's try no to get confused:

We have A:B, with row 1 housing the headers Name and Score.

Do we have in C2 the following?

=LOOKUP(2,1/($A$1:A1=A2),$B$1:B1)

By the way, it would be great if you could post A1:B12 in an Excel readable form (thus not as image or picture).

 

[JumboCactuar]

yes, thats right for C2

[

Name	Score	Lookup	Index
fre	12	=IF(A2="",0,LOOKUP(2,1/($A$1:A1=A2),$B$1:B1))	=IFERROR(INDEX(B$1:B1,1/(1/MAX(INDEX((A$1:A1=A2)*ROW(A$1:A1),)))),0)
John	1	=IF(A3="",0,LOOKUP(2,1/($A$1:A2=A3),$B$1:B2))	=IFERROR(INDEX(B$1:B2,1/(1/MAX(INDEX((A$1:A2=A3)*ROW(A$1:A2),)))),0)
Bob	55	=IF(A4="",0,LOOKUP(2,1/($A$1:A3=A4),$B$1:B3))	=IFERROR(INDEX(B$1:B3,1/(1/MAX(INDEX((A$1:A3=A4)*ROW(A$1:A3),)))),0)
Lim	2	=IF(A5="",0,LOOKUP(2,1/($A$1:A4=A5),$B$1:B4))	=IFERROR(INDEX(B$1:B4,1/(1/MAX(INDEX((A$1:A4=A5)*ROW(A$1:A4),)))),0)
Tim	7	=IF(A6="",0,LOOKUP(2,1/($A$1:A5=A6),$B$1:B5))	=IFERROR(INDEX(B$1:B5,1/(1/MAX(INDEX((A$1:A5=A6)*ROW(A$1:A5),)))),0)
Lim	8	=IF(A7="",0,LOOKUP(2,1/($A$1:A6=A7),$B$1:B6))	=IFERROR(INDEX(B$1:B6,1/(1/MAX(INDEX((A$1:A6=A7)*ROW(A$1:A6),)))),0)
fre	4	=IF(A8="",0,LOOKUP(2,1/($A$1:A7=A8),$B$1:B7))	=IFERROR(INDEX(B$1:B7,1/(1/MAX(INDEX((A$1:A7=A8)*ROW(A$1:A7),)))),0)
John	52	=IF(A9="",0,LOOKUP(2,1/($A$1:A8=A9),$B$1:B8))	=IFERROR(INDEX(B$1:B8,1/(1/MAX(INDEX((A$1:A8=A9)*ROW(A$1:A8),)))),0)
Bob	3	=IF(A10="",0,LOOKUP(2,1/($A$1:A9=A10),$B$1:B9))	=IFERROR(INDEX(B$1:B9,1/(1/MAX(INDEX((A$1:A9=A10)*ROW(A$1:A9),)))),0)
Lim	55	=IF(A11="",0,LOOKUP(2,1/($A$1:A10=A11),$B$1:B10))	=IFERROR(INDEX(B$1:B10,1/(1/MAX(INDEX((A$1:A10=A11)*ROW(A$1:A10),)))),0)
fre	6	=IF(A12="",0,LOOKUP(2,1/($A$1:A11=A12),$B$1:B11))	=IFERROR(INDEX(B$1:B11,1/(1/MAX(INDEX((A$1:A11=A12)*ROW(A$1:A11),)))),0)

<tbody>
</tbody>

 

[Aladin Akyurek]

=IFERROR(INDEX(B$1:B1,1/(1/MAX(INDEX((A$1:A1=A2)*ROW(A$1:A1),)))),0)

uses a risky concept and it's expensive (partly due the IFERROR call).


If you want 0 instead of error values, the LOOKUP can be modified to do so:

=IFERROR(LOOKUP(2,1/($A$1:A1=A2),$B$1:B1),0)

You can replace 0 with "" to get blanks instead of zero's.

We won't be any faster though.

 

[JumboCactuar]

Thankyou, I will use that

Though what is this risky concept?

 

[Aladin Akyurek]

Thankyou, I will use that

You are welcome.

Though what is this risky concept?

One formula you presented has this bit:

1/(1/MAX(INDEX((A$1:A1=A2)*ROW(A$1:A1),)))

This relies on the following equality:

1/(1/X) = X

Example:

1/(1/3) = 3

Alas, it is not always 1/(1/X) = X in the real world, that is, in the computer world.