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?

 

[Aladin Akyurek]

The IF statement that I have which has the ISNA is the following

=IF($AT$4>0,IF(ISNA(INDEX(projsls,(MATCH($B18,Prjsheet,0)))),0,(INDEX(projsls,(MATCH($B18,Prjsheet,0))))/1000),IF(ISNA(INDEX(ladder20,(MATCH($L18,Ladders,0)))),0,(INDEX(ladder20,(MATCH($L18,Ladders,0))))))

projsls is a named range
Prjsheet is a named range
Ladders is a named range
Ladder 20 is a named range

Originally I wrote a version of this formula as a vlookup but i switched my formulas to index match since i could set up a named range that only looked up one column as opposed to my vlookup formula that looked up an entire worksheet in my named range. By using smaller named ranges, will this make my workbook calculate faster?

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)))))

 

[drewbny]

Why would I have to setup Big Num? and how does the choose command work? ALso I saw that you used lookup. I cannot sort my data so lookup will still work?

 

[Aladin Akyurek]

Why would I have to setup Big Num? and how does the choose command work? ALso I saw that you used lookup. I cannot sort my data so lookup will still work?

If you look at the set up closely, it avoids computing the same thing twice.
Also, LOOKUP as used here is a different matter: It does not require any sorting.

 

[drewbny]

If you look at the set up closely, it avoids computing the same thing twice.
Also, LOOKUP as used here is a different matter: It does not require any sorting.

Would this formula calculate extremely fast? is this faster than using Countif to replace the ISNA?

 

[Aladin Akyurek]

Would this formula calculate extremely fast? is this faster than using Countif to replace the ISNA?

It's faster than what you had and certainly faster than CountIf...

 

[drewbny]

Is this the fastest way to write my formula? i used index match so my named ranges are just one column long. WHen i had my formulas as vlookups, i had my named ranges are 50 columns long, and then i would vlookup the column i need. IN this case , is using index match faster?

 

[Aladin Akyurek]

Is this the fastest way to write my formula? i used index match so my named ranges are just one column long. WHen i had my formulas as vlookups, i had my named ranges are 50 columns long, and then i would vlookup the column i need. IN this case , is using index match faster?

Yes, Index/Match is faster...

 

[drewbny]

What does defining Big Number do?

 

[T. Valko]

Why would I have to setup Big Num?

You don't have to.

All you need for the lookup_value is a number that's guaranteed to be greater than the number returned by either of these expressions:

INDEX(projsls,MATCH($B18,Prjsheet,0))/1000

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

For example, if you're dealing with bowling scores then you know the maximum possible score is 300. So, if the value returned by either of those expressions can not possibly be greater than 300 then the lookup_value simply needs to be greater than 300. Like 301.

I typically use 1E100 which is scientific notation for 1 follwed by 100 zeros. That's a really big number and I don't even have to remember how many 9's to enter!

=LOOKUP(301,.......
=LOOKUP(1E100,.......
=LOOKUP(9.99999999999999E+307...

 

[Aladin Akyurek]

What does defining Big Number do?

It is used as lookup value in

LOOKUP(BigNum,Reference)

This returns the last numeric value from Reference:

Example:

A1: 20
A2: 3

LOOKUP(BigNum,A1:A2)

returns 3.

In

LOOKUP(BigNum,CHOOSE({1,2},0,INDEX(...,MATCH(...)))

CHOOSE creates a two-item array like in:

{0,45}

{0,#N/A}

Such fed LOOKUP yields: 45 and 0, respectively.

The way LOOKUP works with the BigNum is explained in post #7 of:

http://www.mrexcel.com/forum/showthread.php?t=310278

BigNum corresponds by the way to a constant you can find under "limits" of Excel's Help.