Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Re: Excel Vlookup

3 views
Skip to first unread message

Carl Witthoft

unread,
Mar 18, 2009, 6:14:55 PM3/18/09
to
Take a look at OFFSET() and possibly INDIRECT(), MATCH() and related
functions.

In article <D6399B33-682C-4149...@microsoft.com>,
Heather <Hea...@discussions.microsoft.com> wrote:

> Hi, is there a way for me to do a lookup that will allow me to pull from the
> row prior? -- any help is greatly appreciated .. :)
>
> If I need it to look up for example 7 -- Instead it's pulling in $75,362 --
> how do I get it to lookup one row prior?
>
> It should be pulling $42,742 + 2 @ $6524 = $55,790
> =VLOOKUP(DealAnnualVolume,'Core
> Platform'!A70:F78,6)+VLOOKUP(DealAnnualVolume,'Core
> Platform'!A70:D78,4)*(DealAnnualVolume-(VLOOKUP(DealAnnualVolume,'Core
> Platform'!A70:F78,1)-1))
>
>
> 1 2 $22,495 N/A $11,248 $22,495
> 3 5 $6,749 $8,548 $42,742
> 6 10 $6,524 $7,536 $75,362
> 11 20 $6,186 $6,861 $137,222
> 21 50 $5,624 $6,119 $305,942
> 51 100 $4,499 $5,309 $530,892
> 101 200 $3,937 $4,623 $924,592
> 201 500 $3,374 $3,874 $1,936,792
> 501 1,000 $2,812 $3,343 $3,342,792

--
Team EM to the rescue! http://www.team-em.com

Laroche J

unread,
Mar 18, 2009, 6:31:26 PM3/18/09
to
Heather wrote on 2009-03-18 11:26:

> Hi, is there a way for me to do a lookup that will allow me to pull from the
> row prior? -- any help is greatly appreciated .. :)
>
> If I need it to look up for example 7 -- Instead it's pulling in $75,362 --
> how do I get it to lookup one row prior?
>
> It should be pulling $42,742 + 2 @ $6524 = $55,790
> =VLOOKUP(DealAnnualVolume,'Core
> Platform'!A70:F78,6)+VLOOKUP(DealAnnualVolume,'Core
> Platform'!A70:D78,4)*(DealAnnualVolume-(VLOOKUP(DealAnnualVolume,'Core
> Platform'!A70:F78,1)-1))
>
>
> 1 2 $22,495 N/A $11,248 $22,495
> 3 5 $6,749 $8,548 $42,742
> 6 10 $6,524 $7,536 $75,362
> 11 20 $6,186 $6,861 $137,222
> 21 50 $5,624 $6,119 $305,942
> 51 100 $4,499 $5,309 $530,892
> 101 200 $3,937 $4,623 $924,592
> 201 500 $3,374 $3,874 $1,936,792
> 501 1,000 $2,812 $3,343 $3,342,792
>


This should do it, if I understand your table correctly:
=VLOOKUP(VLOOKUP(DealAnnualVolume,'Core Platform'!A70:G78,1)-1,'Core
Platform'!A70:G78,6)+VLOOKUP(DealAnnualVolume,'Core
Platform'!A70:E78,4)*(DealAnnualVolume-(VLOOKUP(DealAnnualVolume,'Core
Platform'!A70:G78,1)-1))

I get the #N/A result if DealAnnualVolume equals 1 or 2, because VLOOKUP
tries to look above the specified range (row 69), which is not valid. You
may bypass this by changing A70 for A69 in the formula, putting 0 in cell
A69, and making sure that cell F69 is empty or 0.

As an alternate solution, you could also move down by one row the numbers in
the last column, and using your own formula.

JL
Mac OS X 10.4.11, Office v.X 10.1.9

HS

unread,
Mar 18, 2009, 7:08:05 PM3/18/09
to
Thank you JL -- this works :) except I can't seem to get values of 1 or 2 to
work even when changing it to A69?

Laroche J

unread,
Mar 18, 2009, 10:26:21 PM3/18/09
to
HS wrote on 2009-03-18 19:08:

> Thank you JL -- this works :) except I can't seem to get values of 1 or 2 to
> work even when changing it to A69?
>

OK, to make it clear, the formula should be:

=VLOOKUP(VLOOKUP(DealAnnualVolume,'Core Platform'!A69:G78,1)-1,'Core
Platform'!A69:G78,6)+VLOOKUP(DealAnnualVolume,'Core
Platform'!A69:E78,4)*(DealAnnualVolume-(VLOOKUP(DealAnnualVolume,'Core
Platform'!A69:G78,1)-1))

You don't move your initial table on rows 70-78, but above it (row 69) you
make sure that A69 and F69 both contain 0. You might as well give a name to
the range 'Core Platform'!A69:G78, it would clarify your formula and make it
easy to move or expand the range without retouching the formula.

JL

0 new messages