Vlookup 2 values (looking for approximate values)

[tianyi86]

I am trying to do a vlookup function of 2 values but the key lookup column is an approximation.

I have attached a sample data file. Essentially, on Sheet 1 Column M (labeled as EPS), I want to do a vlookup of both Column A and B, and copy the values in Sheet 2 Column K into Column M. The approximate value comes from Column B, the date, as Sheet 1 has all the dates from 2001 and 2008 while Sheet 2 only has 4 dates per year. On sheet 2, the first 2 dates are 10/30/2001 and 2/5/2002. I want the EPS value for 10/30/2001 to stand for all values between 10/30/2001 and 2/5/2002.

The Vlookup function with a True value for the last criterion can do this for a vlookup of 1 variable, but it doesn't work for 2. Can someone please help?


Thanks
Kevin

[DonkeyOte]

If I've understood...

Sheet1

M2:
=LOOKUP(2,1/((Sheet2!$A$1:$A$100=$A2)*(Sheet2!$B$1:$B$100<=$B2)),Sheet2!$K$1:$K$100)
copied down

NOTES:

1.
Where Date precedes 31/10/2001 you will get an Error value... to rectify (and to avoid need for modification of above) I would advise you add a dummy row to Sheet2 with say date of 1900/01/01 and apply to that the default EPS value you want applied to these early records.

2.
The LOOKUP approach is assuming per your sample that the Dates on Sheet2 are always listed in Ascending order (per Company).

[Steve R]

See attached workbook. Maybe i missunderstood you.

[DonkeyOte]

Hi Steve, man you LOVE your array's

As shown Array's in this instance are not required so best avoided.

I know I've said this before but wherever they (arrays) can be avoided they should be... Array's are almost the last resort IMO given their impact on model performance. I regularly see threads where people have lots of arrays that cause their models to seize.. in fact there was one 2 days ago where a model with a high volume of arrays was taking over 40 minutes to calculate! It transpired that without helpers the formulae could be reworked using non-array methods and the result was 47000 calculations taking 10 seconds... the performance impact of arrays can be that significant.

[Steve R]

I know you love my arrays. If my solution isnīt working out very well, i promise i will come up with another solution.

I will continue posting array formulas, sorry about that! They are clean and i avoid "helper" columns and sheets in workbooks.

[DonkeyOte]

And thus I will continue to advise OP otherwise...

As shown in this instance arrays are not necessary, nor are helper cells thus an array method is a non-sensical one, it adds no value to the model only inefficiency.

If you believe a "clean" & slow model is preferable to an efficient one then you have an interesting take on how to use XL optimally.

[Steve R]

Yes, your solution is easier and more efficient in large workbooks. No question about that.

[tianyi86]

If I've understood...

Sheet1

M2:
=LOOKUP(2,1/((Sheet2!$A$1:$A$100=$A2)*(Sheet2!$B$1:$B$100<=$B2)),Sheet2!$K$1:$K$100)
copied down

NOTES:

1.
Where Date precedes 31/10/2001 you will get an Error value... to rectify (and to avoid need for modification of above) I would advise you add a dummy row to Sheet2 with say date of 1900/01/01 and apply to that the default EPS value you want applied to these early records.

2.
The LOOKUP approach is assuming per your sample that the Dates on Sheet2 are always listed in Ascending order (per Company).

Can you please explain how this works:
=LOOKUP(2,1/((Sheet2!$A$1:$A$100=$A2)*(Sheet2!$B$1:$B$100<=$B2)),Sheet2!$K$1:$K$100)

I sorta understand that the 2 specifies you are looking for 2 values, and the 3rd criteria defines the return values, but how does the middle terms work?

[tianyi86]

This works for the sample sheet but when I apply the formula to my entire data sheet, it stops working. Can someone explain to me exactly that DonkeyOte did please so I can fix it on my main sheet?

Thanks

[DonkeyOte]

LOOKUP(2,1/((Sheet2!$A$1:$A$100=$A2)*(Sheet2!$B$1:$B$100<=$B2)),Sheet2!$K$1:$K$100)

If you consider Lookup used in this fashion works along the lines of:

LOOKUP(lookup_value, lookup_vector, result_vector)

LOOKUP will essentially return the value from the result_vector associated with the greatest value from the lookup_vector that is <= lookup_value, eg:

=LOOKUP(1,{0,1,2},...) --> returns 1 (biggest value <= criteria)

So in conjunction with result_vector...

=LOOKUP(1,{0,1,2},{"a","b","c"}) --> returns "b" (associated with 1 as c is tied to 2)

As you can see LOOKUP assumes your lookup_vector is sorted in ascending order... if not it will (using binary search algorithm) return the last value <= lookup_value, eg:

=LOOKUP(1,{1,0,2},{"b","a",c"}) --> returns "a"
this is because the last value in the lookup_vector <= 1 is 0, this is associated with "a" in the result_vector

In the formula provided you have an unsorted range as outlined above...why ?

LOOKUP(2,1/((Sheet2!$A$1:$A$100=$A2)*(Sheet2!$B$1:$B$100<=$B2)),Sheet2!$K$1:$K$100)

The above (red) generates an array of values where said values are either 1 or #DIV0! ... this is because the calculation is 1 divided by either 1 or 0. The divisor (1 or 0) is determined by whether or not the values on Sheet1 tie out to our criteria... ie TRUE * TRUE = 1 ... 1/1 = 1, if either condition fail we end up multiplying something by FALSE (eg TRUE * FALSE or FALSE * FALSE or FALSE * TRUE) ... either way the result is 0 ... this is because True/False when "coerced" (by *) to integer equate to 1/0 respectively.

So you have an array of 100 values along the lines of:

{1/1,1/0,....,1/1} --> {1,#DIV/0!,....,1}

The LOOKUP will ignore values in the lookup_vector that are not of the same datatype as the lookup_value... this includes errors...

So given the Lookup_Value is 2 the LOOKUP essentially finds the last instance of 1 given this is <= criteria... it returns the associated value from the result_vector.

Re: your last post - you will need to furnish us with further details.

[tianyi86]

Sorry, I was away for a week and didn't see your responses.

Your solution worked well and the problem I had before was due to data formatting.

As a follow up question, is there a way to speed up vlookup?

Similar to the data above, I concaconated company name and date to create a 3rd variable to do vlookup. However, I am looking up values for 700k rows of data and 40 columns. A simple vlookup with a false parameter in the end is going to take 50+ hours to complete. Is there a faster way to do lookups for large sets of data?