Excel Vlookup

[Heather]

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'!A7078,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

 

[Carl Witthoft]

Take a look at OFFSET() and possibly INDIRECT(), MATCH() and related
functions.

 

[Laroche J]

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'!A7078,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]

Thank you JL .. I seem to still be struggling when I place a 1 or 2 in
the box even when I add the 0 and go to A69? Any ideas?

Thanks again!!!!

 

[HS]

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]

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