INDEX, MATCH value lookup based on >= and <=conditions

[bdavis30]

Hello,

I'm trying to create a dynamic value lookup with INDEX and MATCH within specific >= and <= conditions. My hypothetical situation is as follows: my company issues 25 page receipt books with carbon copy pages. The truck drivers we issue the books to have them filled out by the person that accepts the delivery, and they send the carbon copy back to us. I'm trying to somewhat automate the reconciliation of these carbon copies by having the driver information pulled with the INDEX and MATCH functions based on the book and page number of the receipt that I get back. I'm having an issue with this because books can be turned in and signed back out. therefore, a single book can be issued to 2 different drivers/companies. Because of this, I need the INDEX and MATCH formula to first look at book number then the starting and ending page numbers to pull the correct name. In my example below, I want to have column H auto-populate based on the values in columns F and G, respectively. I know my first MATCH is on column A, but how do I work >= column B and <= column C into the same



Thanks!

 

[Peter Bartholomew]

One formula that introduces multiple criteria is LOOKUP
= LOOKUP( 1, 1 / (Receipt[Book]=[@Book]) / (Receipt[Start Page]<=[@Page]) / (Receipt[End Page]>=[@Page]), Receipt[Hauler] )
in which errors are deliberately generated to eliminate failed matches.

 

[Peter Bartholomew]

Further variants. You said you wanted INDEX/MATCH
= INDEX( Receipt[Hauler], MATCH( 1, IF( (Receipt[Book]=[@Book])*(Receipt[Start Page]<=[@Page])*(Receipt[End Page]>=[@Page]), 1 )))
or for Office 365 users
= FILTER( Receipt[Hauler], (Receipt[Book]=[@Book]) * (Receipt[Start Page]<=[@Page]) * (Receipt[End Page]>=[@Page]), "Not found" )

 

[bosco_yip]

Or, try this non-array formula

In H3, copied down :

=LOOKUP(1,0/(A$2:A$7=F3)/(B$2:B$7<=G3),D$2:D$7)

Regards
Bosco

 

[Peter Bartholomew]

@bosco_yip By 'non-array' I assume you mean simply that CSE is not required? In the past my way of ensuring single cell array formulas were evaluated correctly was to use a named formula to hold the array calculation steps. Now, with dynamic arrays, I only need do that if it adds clarity to the calculation.

I found your use of a 0 numerator intriguing, I had only ever thought to use 1. Shouldn't there, however, be a '>=' condition on page
=LOOKUP(1,0/(A$2:A$7=F3)/(B$2:B$7<=G3)/(C$2:C$7>=G3),D$2:D$7)

 

[bdavis30]

@bosco_yip By 'non-array' I assume you mean simply that CSE is not required? In the past my way of ensuring single cell array formulas were evaluated correctly was to use a named formula to hold the array calculation steps. Now, with dynamic arrays, I only need do that if it adds clarity to the calculation.

I found your use of a 0 numerator intriguing, I had only ever thought to use 1. Shouldn't there, however, be a '>=' condition on page
=LOOKUP(1,0/(A$2:A$7=F3)/(B$2:B$7<=G3)/(C$2:C$7>=G3),D$2:D$7)

This worked perfectly @Peter Bartholomew! And thank you for introducing me to the LOOKUP function as I've only ever used VLOOKUP, INDEX, and MATCH. I have to admit that I'm unfamiliar with how array formulas work, so this was something I could easily adapt to my actual dataset.

 

[bosco_yip]

@bosco_yip By 'non-array' I assume you mean simply that CSE is not required? In the past my way of ensuring single cell array formulas were evaluated correctly was to use a named formula to hold the array calculation steps. Now, with dynamic arrays, I only need do that if it adds clarity to the calculation.

I found your use of a 0 numerator intriguing, I had only ever thought to use 1. Shouldn't there, however, be a '>=' condition on page
=LOOKUP(1,0/(A$2:A$7=F3)/(B$2:B$7<=G3)/(C$2:C$7>=G3),D$2:D$7)

1] Lookup function as same as Sumproduct function both need not required CSE or array entry.
Your post #.02 Lookup formula is a non-array formula.
But, your post #.03 Index+Match formula is an array formula.

2] Using LOOKUP(1,0/(Condition1),ResultRange) is as same as LOOKUP(2,1/(Condition1),ResultRange), but never use LOOKUP(1,1/(Condition1),ResultRange) which it is a risky formula.

Logic of this formula is using BigNum and return the last value.
the LOOKUP(1,0/(...),…) or LOOKUP(2,1/(...),…) style is a short form or lazy form, the most safety form formula using is :
=LOOKUP(9.99999999999999E+307,1/(…...),….) --> 9.99999999999999E+307 is the biggest number in Excel.

LOOKUP(1,1/(...),…) is a risky form, because the LookupValue "1" is not a BigNum and may not return the last value

Try to Google in search "BigNum" for more detailing. (Or you can search in MrExcel.com for Aladin, he is the founder of "BigNum" at around 2003)

3] The post #.01 "grading table" with lower bound and upper bound list, in using Lookup function the upper bound is not required, so, " '>=' condition " can be saved and my formula in post #.04 is enough.

Regards
Bosco

 

[Peter Bartholomew]

Bosco
Thank you for your observations.
1] I would still regard them [lookup and sumproduct] as array calculations but I see where you are coming from.
I should have drawn attention to the fact that the MATCH formula needs CSE; I was using Office 365 insider so it wasn't an issue for me.
2] I tested LOOKUP(1,1/(Condition1),ResultRange) and found that it tended to find the first match in the second half of the list rather than the last occurrence. Where the match was unique, the formula always found it.
3] I did not rely on the order of the records as presented, so I needed the additional condition. Provided the pages of a given book are always referenced in order, your reduced formula works well; clever!
Peter

 

[XOR LX]

@bosco_yip

I have to disagree with one of your observations: there is nothing "short" or "lazy" about using 1 (or 0) as the lookup_value in such a construction.

In fact, if the lookup_vector has first been reciprocated with unity (or zero), then using any value greater than 2 (or 1) is entirely redundant. The use of 'BigNum' is only appropriate if the lookup_vector contains arbitrarily large values.

Regards

 

[Lori]

9.99999999999999E+307 is the biggest number in Excel.

That's the largest numeric constant but also may give a misleading impression of reliability as bigger numbers are easily created with formulas, eg '=10^308'. Even if a lookup range contains only constants, the definition BigNum '=9.99999999999999E+307' may fail if BigNum occurs more than once in the range as mentioned above.

More robust is to use the largest double value '=2^971*(2^53-1)' or a special number via a UDF (https://chandoo.org/forum/threads/largest-number.37272)

For practical purposes one can choose the value according to context. For example if the list consists of character positions within a string, the lookup value could be '=8^5' which is one greater than the maximum text length of a cell.

 

[Lori]

@XOR LX Agreed, after looking at the BigNum search results i still don't see why one would possibly want to choose a value consisting of 21 characters when a single digit will do?

In general choosing 'the least integer greater than all values in the lookup array' gives 10^308 for arbitrary constants and {1.79769313486233E+308} for calculated values (to 15sf).

Here, the array constant is defined as a binary name (xlsb) via the code line: 'Names.Add "Inf", Array(INF())' and INF() is as in the previous link.