Help with complex VLOOKUP

Dear experts,

I've inherited a very large set of spreadsheets.
Daily, I get data from other spreadsheets, and paste it into this one.
And, I run database queries, and paste those in too.
Sometimes, we get bad errors, and spend a long time figuring things
out.


The person who wrote them is not here. She used the VLOOKUP
function all over the place.

I've tried a very simple VLOOKUP.

=VLOOKUP(A27, $B$3:$B$262, 1, FALSE)

This means:
take the value in cell a27,
Search through cells b3 to b262
if found, put the value for a27 into the cell.


-----------

But what the other writer did is beyond me. It looks like this:

VLOOKUP($R243,'another worksheet'!$A:$Q,Y$2+8,FALSE)*$W243,

and

VLOOKUP($R243,'Partial ratios'!$A:$O,Y$2,FALSE)*$W243


Just what exactly does this mean???

'another worksheet'!$A:$Q,Y$2+8,
'another worksheet'!$A:$O,Y$2,

What range is it searching?


I am hoping to get answers from people who speak from experience.


Thanks a lot!

In each case, the search is still in column A of the named sheet ('another
worksheet' or 'Partial ratios'; check the sheet names on the worksheet tabs
to figure out which sheet it's searching). The big difference is that
instead of returning the value from column A (specified by the 1 in the first
equation) of the matching row, it's returning the value from the column
specified in cell Y2 (or that cell plus 8).

"dba_222@yahoo.com" wrote:

> Dear experts,
>
> I've inherited a very large set of spreadsheets.
> Daily, I get data from other spreadsheets, and paste it into this one.
> And, I run database queries, and paste those in too.
> Sometimes, we get bad errors, and spend a long time figuring things
> out.
>
>
> The person who wrote them is not here. She used the VLOOKUP
> function all over the place.
>
> I've tried a very simple VLOOKUP.
>
> =VLOOKUP(A27, $B$3:$B$262, 1, FALSE)
>
> This means:
> take the value in cell a27,
> Search through cells b3 to b262
> if found, put the value for a27 into the cell.
>
>
> -----------
>
> But what the other writer did is beyond me. It looks like this:
>
> VLOOKUP($R243,'another worksheet'!$A:$Q,Y$2+8,FALSE)*$W243,
>
> and
>
> VLOOKUP($R243,'Partial ratios'!$A:$O,Y$2,FALSE)*$W243
>
>
> Just what exactly does this mean???
>
> 'another worksheet'!$A:$Q,Y$2+8,
> 'another worksheet'!$A:$O,Y$2,
>
> What range is it searching?
>
>
> I am hoping to get answers from people who speak from experience.
>
>
> Thanks a lot!
>
>

On 15 Nov 2005 11:26:11 -0800, dba_222@yahoo.com wrote:

>Dear experts,
>
>I've inherited a very large set of spreadsheets.
>Daily, I get data from other spreadsheets, and paste it into this one.
>And, I run database queries, and paste those in too.
>Sometimes, we get bad errors, and spend a long time figuring things
>out.
>
>
>The person who wrote them is not here. She used the VLOOKUP
>function all over the place.
>
>I've tried a very simple VLOOKUP.
>
>=VLOOKUP(A27, $B$3:$B$262, 1, FALSE)
>
>This means:
>take the value in cell a27,
>Search through cells b3 to b262
>if found, put the value for a27 into the cell.
>
>
>-----------
>
>But what the other writer did is beyond me. It looks like this:
>
>VLOOKUP($R243,'another worksheet'!$A:$Q,Y$2+8,FALSE)*$W243,
>
>and
>
>VLOOKUP($R243,'Partial ratios'!$A:$O,Y$2,FALSE)*$W243
>
>
>Just what exactly does this mean???
>
>'another worksheet'!$A:$Q,Y$2+8,
>'another worksheet'!$A:$O,Y$2,
>
>What range is it searching?


She is searching the range 'another worksheet'!$A$1:$Q$65536

The Y$2 (or Y$2+8) should resolve to a number in the range of 1-17 which will
represent the column within A:Q to return. If it resolves to a 3, then you
will return the value in column C where the value in the same row of Column A
is the same as the contents of $R243.

Then that returned value is multiplied by the contents of $W243





>
>
>I am hoping to get answers from people who speak from experience.
>
>
>Thanks a lot!

--ron

It means, in 'plain' English...

Look on the sheet called 'another worksheet' in colums A to Q to find
an exact match of the value in cell R243. When found, return the value
in the column number (value in Y2) + 8 that corresponds with this
value*. Multiply the returned vlaue by the contents of cell W243.

*Column A will be column '1', B = '2' etc...