We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Excel syntax range=value in lookup function

jeffr
jeffr asked
on
Medium Priority
325 Views
Last Modified: 2012-08-13
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)?

Comment
Watch Question

Commented:
Those are the conditions for you lookup value to find...

Commented:
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

Author

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?

CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Thanks barryhoudini.  Is there some way that I can display the array showing the 1's and 0's?
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
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

Author

Commented:
Excellent response

Author

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?
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
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

Author

Commented:
Another great explanation Barry.  Thanks again!

Regards,
Jeff
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.