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

=LOOKUP(2,1/(('worksheetA'

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

=SUMPRODUCT((Sheet2!A1:A14

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

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

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

{FALSE;FALSE;TRUE;FALSE;FA

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

1/(('worksheetA'!$C$2:$C$2

it should look like this

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

regards, barry

=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

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.