Thread
:
Help with complex VLOOKUP
View Single Post
#
3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel.misc
Ron Rosenfeld
Posts: n/a
Help with complex VLOOKUP
On 15 Nov 2005 11:26:11 -0800,
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
Reply With Quote