vlookup- approximate match

[drewbny]

I tried using vlookup with approximate match and my vlookups werent working. I have to lookup a column which is a concatenate of two other columns. does vlookups on approximate match not work with concatenates?

 

[drewbny]

Ok thanks. ALso I am trying to convert my original formula using the ISNA to the new formula which uses the choose function. Is there a quick way I convert the formulas? I tried doing a search and replace to swap out the syntax but i get an error because Im not typing in the entire formula to replace . Im just trying to swap out the ISNA( with Choose{1,2"

 

[Aladin Akyurek]

Ok thanks. ALso I am trying to convert my original formula using the ISNA to the new formula which uses the choose function. Is there a quick way I convert the formulas? I tried doing a search and replace to swap out the syntax but i get an error because Im not typing in the entire formula to replace . Im just trying to swap out the ISNA( with Choose{1,2"

I think you should do this either manually or sollicite for code in VBA.

 

[drewbny]

HI Aladin, I tried using this formula and for some reason its looking up the wrong information? My data is not sorted. Will this formula work on unsorted data? Also it seems like this formula isn't making my sheet calculate any faster. Let me know what I could possibly be doing wrong?
Here is the formula i have

=IF($AT$4>0,LOOKUP(bignum,CHOOSE({1,2},0,INDEX(projsls,(MATCH($B29,Prjsheet,0))))/1000),LOOKUP(bignum,CHOOSE({1,2},0,INDEX(ladder20,(MATCH($L29,Ladders,0))))))

Define BigNum as referring to:

=9.99999999999999E+307

Then invoke:

=IF($AT$4>0,
  LOOKUP(BigNum,CHOOSE({1,2},0,INDEX(projsls,MATCH($B18,Prjsheet,0))/1000)),
  LOOKUP(BigNum,CHOOSE({1,2},0,INDEX(ladder20,MATCH($L18,Ladders,0)))))

 

[Aladin Akyurek]

HI Aladin, I tried using this formula and for some reason its looking up the wrong information?

What do you mean by wrong information?

My data is not sorted. Will this formula work on unsorted data?

The formula does not require sorted data.

LOOKUP with BigNum as look up value picks out the last numeric value from a 2-item array CHOOSE constructs from 0 and Index?Match result.

Also it seems like this formula isn't making my sheet calculate any faster. Let me know what I could possibly be doing wrong?

...

The formula as is avoids computing the same thing many times. A different formula would make your wb slower.

 

[drewbny]

HI, for instance the formula

=IF($AT$4>0,
LOOKUP(BigNum,CHOOSE({1,2},0,INDEX(projsls,MATCH($B18,Prjsheet,0))/1000)),
LOOKUP(BigNum,CHOOSE({1,2},0,INDEX(ladder20,MATCH($L18,Ladders,0)))))

In my workbook, cell $AT$4 is greater than 0, so the formula is choosing the 2nd formula which is ,INDEX(ladder20,MATCH($L18,Ladders,0). Cell $L18 for me is this year sales for a class number 15065403. When i check what information the formula is pulling, its pulling in the last year sales for class number 15065403. Whats even more strange is when I sorted the data in the Ladders tab, my end result became a different number.

 

[drewbny]

HI, for instance the formula

=IF($AT$4>0,
LOOKUP(BigNum,CHOOSE({1,2},0,INDEX(projsls,MATCH($B18,Prjsheet,0))/1000)),
LOOKUP(BigNum,CHOOSE({1,2},0,INDEX(ladder20,MATCH($L18,Ladders,0)))))

In my workbook, cell $AT$4 is greater than 0, so the formula is choosing the 2nd formula which is ,INDEX(ladder20,MATCH($L18,Ladders,0). Cell $L18 for me is this year sales for a class number 15065403. When i check what information the formula is pulling, its pulling in the last year sales for class number 15065403. Whats even more strange is when I sorted the data in the Ladders tab, my end result became a different number.

 

[Aladin Akyurek]

HI, for instance the formula

=IF($AT$4>0,
LOOKUP(BigNum,CHOOSE({1,2},0,INDEX(projsls,MATCH($B18,Prjsheet,0))/1000)),
LOOKUP(BigNum,CHOOSE({1,2},0,INDEX(ladder20,MATCH($L18,Ladders,0)))))

In my workbook, cell $AT$4 is greater than 0, so the formula is choosing the 2nd formula which is ,INDEX(ladder20,MATCH($L18,Ladders,0). Cell $L18 for me is this year sales for a class number 15065403. When i check what information the formula is pulling, its pulling in the last year sales for class number 15065403. Whats even more strange is when I sorted the data in the Ladders tab, my end result became a different number.

INDEX(ladder20,MATCH($L18,Ladders,0))

is yours, so what it returns is not due to the LOOKUP/CHOOSE set up.

You need to assess/determine yourself why this formula expression is not returning what you expect. Note that approximate and exact matches work differently...

 

[drewbny]

My index match formula is setup to pull an exact match(setup as 0). Why would my end results change based on sorting or anything else? Is there something Im missing about index/match?

 

[Aladin Akyurek]

My index match formula is setup to pull an exact match(setup as 0). Why would my end results change based on sorting or anything else? Is there something Im missing about index/match?

1]

=VLOOKUP(A2,$E$2:$G$400,3,0)

2]

=INDEX($G$2:$G$400,MATCH(A2,$E$2:$E$400,0))


[1] and [2] are identical in retieval behavior.

If $E$2:$G$400 is sorted in ascending order on E2:E400, the following is also identical in behavior to [1] and [2]...

3]

=IF(LOOKUP(A2,$E$2:$E$400)=A2,LOOKUP(A2,$E$2:$E$400,$G$2:$G$400),"")

Qua performance:

[3] is the fastest, [2] is somewhat better than [1].

All of the preceding formulas do exact matching.

_____________________________________________

Consider the table in A2:B5, which is sorted on A2:A5 in ascending order...

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>FAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>40</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>KAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>85</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>LAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>97</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>KAD</TD></TR></TBODY></TABLE>

4]

=VLOOKUP(F2,$A$2:$B$5,2,1)

5]

=INDEX($B$2:$B$5,MATCH(F2,$A$2:$A$5,1))

6]

=LOOKUP(F2,$A$2:$A$5,$B$2:$B$5)

[4] to [6] do all approximate matching and all are very fast.

_____________________________________________


7]

=LOOKUP(9.99999999999999E+307,{0,#N/A})
=VLOOKUP(9.99999999999999E+307,{0;#N/A},1,1)
=INDEX({0,#N/A},MATCH(9.99999999999999E+307,{0,#N/A},1))

8]

=LOOKUP(9.99999999999999E+307,N2:N100)
=VLOOKUP(9.99999999999999E+307,N2:N100,1,1)
=INDEX(N2:N100,MATCH(9.99999999999999E+307,N2:N100,1))

9]

=LOOKUP(9.99999999999999E+307,N:N)
=VLOOKUP(9.99999999999999E+307,N:N,1,1)
=INDEX(N:N,MATCH(9.99999999999999E+307,N:N,1))

10]

=LOOKUP(9.99999999999999E+307,4:4)
=VLOOKUP(9.99999999999999E+307,4:4,1,1)
=INDEX(4:4,MATCH(9.99999999999999E+307,4:4,1))

All of [7] to [10] picks out the last numeric value from the reference they are given. And they do so very fast.

If you replace 9.99999999999999E+307 with REPT("z",255), these formulas will pick out the last text value from the reference they are given. And they will do so very fast.

Hope this helps you sort out matters retrieval...

 

[drewbny]

Thanks Aladin. I found out the issue with my worksheet. I had dynamic named ranges that were starting at different locations. Thanks so much for your help. Formula works!!

1]

=VLOOKUP(A2,$E$2:$G$400,3,0)

2]

=INDEX($G$2:$G$400,MATCH(A2,$E$2:$E$400,0))


[1] and [2] are identical in retieval behavior.

If $E$2:$G$400 is sorted in ascending order on E2:E400, the following is also identical in behavior to [1] and [2]...

3]

=IF(LOOKUP(A2,$E$2:$E$400)=A2,LOOKUP(A2,$E$2:$E$400,$G$2:$G$400),"")

Qua performance:

[3] is the fastest, [2] is somewhat better than [1].

All of the preceding formulas do exact matching.

_____________________________________________

Consider the table in A2:B5, which is sorted on A2:A5 in ascending order...

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" align=right width=64 height=19>0</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>FAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" align=right height=19>40</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">KAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" align=right height=19>85</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">LAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.4pt; BACKGROUND-COLOR: transparent" align=right height=19>97</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">KAD</TD></TR></TBODY></TABLE>

4]

=VLOOKUP(F2,$A$2:$B$5,2,1)

5]

=INDEX($B$2:$B$5,MATCH(F2,$A$2:$A$5,1))

6]

=LOOKUP(F2,$A$2:$A$5,$B$2:$B$5)

[4] to [6] do all approximate matching and all are very fast.

_____________________________________________


7]

=LOOKUP(9.99999999999999E+307,{0,#N/A})
=VLOOKUP(9.99999999999999E+307,{0;#N/A},1,1)
=INDEX({0,#N/A},MATCH(9.99999999999999E+307,{0,#N/A},1))

8]

=LOOKUP(9.99999999999999E+307,N2:N100)
=VLOOKUP(9.99999999999999E+307,N2:N100,1,1)
=INDEX(N2:N100,MATCH(9.99999999999999E+307,N2:N100,1))

9]

=LOOKUP(9.99999999999999E+307,N:N)
=VLOOKUP(9.99999999999999E+307,N:N,1,1)
=INDEX(N:N,MATCH(9.99999999999999E+307,N:N,1))

10]

=LOOKUP(9.99999999999999E+307,4:4)
=VLOOKUP(9.99999999999999E+307,4:4,1,1)
=INDEX(4:4,MATCH(9.99999999999999E+307,4:4,1))

All of [7] to [10] picks out the last numeric value from the reference they are given. And they do so very fast.

If you replace 9.99999999999999E+307 with REPT("z",255), these formulas will pick out the last text value from the reference they are given. And they will do so very fast.

Hope this helps you sort out matters retrieval...