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

#### JumboCactuar

##### Well-known Member
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
Code:
=LOOKUP(2,1/(\$A\$1:A2=A2),\$B\$1:B2)

Someone on Reddit gave me this alternative

Index Column D
Code:
=IFERROR(INDEX(B\$1:B1,1/(1/MAX(INDEX((A\$1:A1=A2)*ROW(A\$1:A1),)))),"")

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

Can anyone come up with anything quicker?

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

##### MrExcel MVP
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

##### Well-known Member
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

Rich (BB code):
=LOOKUP(2,1/(\$A\$1:A1=A2),\$B\$1:B1)

##### MrExcel MVP
Ye it's meant to be

Rich (BB code):
=LOOKUP(2,1/(\$A\$1:A1=A2),\$B\$1:B1)

Are you entering it in C1 or C2?

#### JumboCactuar

##### Well-known Member
Are you entering it in C1 or C2?

in C2, works as intended

results

Last edited:

##### MrExcel MVP
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

##### Well-known Member
yes, thats right for C2

[
NameScoreLookupIndex
fre12=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)
John1=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)
Bob55=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)
Lim2=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)
Tim7=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)
Lim8=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)
fre4=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)
John52=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)
Bob3=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)
Lim55=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)
fre6=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>

Last edited:

##### MrExcel MVP
=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

##### Well-known Member
Thankyou, I will use that

Though what is this risky concept?

##### MrExcel MVP
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.

Replies
3
Views
1K
Replies
0
Views
624
Replies
1
Views
429
Replies
0
Views
524
Replies
7
Views
600

1,195,683
Messages
6,011,138
Members
441,587
Latest member
kbsgiri09

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back