• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

Excel syntax range=value in lookup function

In Microsoft Excel, what does the syntax range=value in the following formula do exactly?  That is, what is being returned by ('worksheetA'!$C$2:$C$25="something") in the following formula)

 =LOOKUP(2,1/(('worksheetA'!$C$2:$C$25="something")*('worksheetA'!$D$2:$D$25="something else")*('worksheetA'!$E$2:$E$25="another thing")),('worksheetA'!$F$2:$F$25))

Please explain.  I understand the lookup command but the parameters above are not familiar to me where the ranges are being compared to values.  Where in the online help will I find some information about this syntax (not the lookup function but the range=value syntax)?

0
jeffr
Asked:
jeffr
  • 5
  • 3
  • 2
1 Solution
 
jppintoCommented:
Those are the conditions for you lookup value to find...
0
 
jppintoCommented:
It more or less like the SUMPRODUCT() function. If you do a sumproduct function like this:

=SUMPRODUCT((Sheet2!A1:A14="something")*(Sheet2!F1:F14="other thing")*(Sheet2!J1:J14))

it will just return the value from column J corresponding to the row where it finds "something" on column A and "other thing" on column F.

Hope that I've made myself clear enought...

jppinto
0
 
jeffrAuthor Commented:
To clarify my question I will simplify my lookup function example because I don't care that it is multiplying and I should have left that out to highlight my actual question.  And I also understand that this represents the lookup value that I want to find.  I copied this solution from another website and it performs in the way that I need it to.  I am familiar with the lookup function and its parameters in the general cases where I've previously used it.  I just want to fully understand what this particular syntax is doing.  

The question is, what does the syntax range=value actually return to the lookup function.  Here is a simpler version so that we are focused on the same thing  --

=LOOKUP(2,1/(('worksheetA'!$C$2:$C$25="something)),('worksheetA'!$F$2:$F$25))

so, in summary, what does syntax ("worksheetA"!$C$2:$C$25="something") perform?  Does it return a boolean? A cell? A value?

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
barry houdiniCommented:
It returns an array of values, TRUE/FALSE, so as you have 24 cells in the range C2:C25 it will return an array of 24 values, one for each cell in that range, when 1 is divided by that array the TRUEs become 1s and the FALSEs become #DIV/0! errors. Lookup then matches with the last numeric value in that array (which must be the last 1), so the formula returns the corresponding cell in F2:F25 matching the last C2:C25 cell that = "something"

regards barry
0
 
jeffrAuthor Commented:
Thanks barryhoudini.  Is there some way that I can display the array showing the 1's and 0's?
0
 
barry houdiniCommented:
You won't get 1s and zeroes. You'll either get TRUEs and FALSEs before the division of 1 by that arry.......or 1s and #DIV/0!s after the division. You can use formula evaluation but a simpler way is just to select the cell with the formula and then select the relevant part of the formula in the formula bar, i.e. just this part

'worksheetA'!$C$2:$C$25="something

Then press F9 - you should see an array that looks something like this

{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;..........TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

or if you select this part before pressing F9.....

1/(('worksheetA'!$C$2:$C$25="something))

it should look like this

{#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;........1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}

regards, barry
0
 
jeffrAuthor Commented:
Excellent response
0
 
jeffrAuthor Commented:
If you're still watching the question barry, I think I can guess the answer, but why is the lookup finding the value "2" if the last cell in the range is being returned?
0
 
barry houdiniCommented:
if you read up on LOOKUP in the help files you'll see that the LOOKUP range ought to be sorted in ascending order. In this case that's the array of 1s and #DIV/0! errors and, of course, it isn't sorted in ascending order.......but there's a "quirk" that makes that work in our favour. When the lookup value is greater than any value in the lookup range the last numeric value is returned so we can use this formula to find the last number in column A

=LOOKUP(9.9E+307,A:A)

9.9E+307 is a very "Bignum" and we won't expect to see a value that large in column A.....so because no matching value is found the formula returns the last value in A:A.

Similarly because no value in our lookup range is greater than 1 here 2 can safely be used as the lookup value. No value in the lookup range is as great as 2....so the match is with the last number (in this case the last 1). Any value >= 1 will work so the lookup value can be 1....or 101 or 1000000......the formula will still return the same result. Using 2 as the lookup value has become something of a "convention" among contributors to the excel forums that I frequent....so that's the value I use

regards, barry
0
 
jeffrAuthor Commented:
Another great explanation Barry.  Thanks again!

Regards,
Jeff
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now