Solved

Posted on 2011-04-25

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

10 Comments

=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

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'

so, in summary, what does syntax ("worksheetA"!$C$2:$C$25="

regards barry

'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

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

wb.Worksheets(1).Range("A1 |
3 | 22 | |

copying from excel to word | 2 | 28 | |

Using Pop Up Calculator in Excel | 4 | 27 | |

Updating Pivot Table within VBA | 5 | 28 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**22** Experts available now in Live!