Basic Excel Functions driving me nuts, help please

[gatorduck]

Hello Everyone!
I have been at it for atleast 4 hours, no kidding, trying to figure out what to do. I have office 07 version of excel. First off let me explain my situation, I have four columns. The first one is my named 'ABC', the second one is where I want my results to go into, and that is named 'results', and the last two columns are where I get my information from. Are you still with me? Moving on, so here is what I am doing, lets take our first example, ABC 1 (CELL #A2), searches my for a corresonding value located at #C7, and retrieves the price for ABC 1, which is located in D#7, and retrieves my information to my column named 'Results'. Pretty easy huh, the function for that is simply =VLOOKUP(A2, $C$2:$D$7, 2,FALSE).

Now here is what kills me, lets take ABC 6(#A7), if we look it up, it has no price, instead it says call rep.....here is my issue, how can I select the price above it, in this case a price of $20(#D5). I have tried everything to no avail, I would really appreciate the help.

Here is what I have tried

=IF((VLOOKUP(A8,$C$2:$D$11,2,FALSE)="Call Rep"), <-- my condition
OFFSET(D7,-1,0), < -- if true, that means there is no price, just Call Rep, so I'm trying to get the price in the cell above this Call Rep
(VLOOKUP(A8,$C$2:$D$11,2,FALSE))) <-- if false, this will just give me my normal price

here is my image:

 

[Haseeb Avarakkan]

Hello, Try,

=LOOKUP(1E+100,CHOOSE({1,2},0,INDEX($D$2:$D$16,MATCH(A2,$C$2:$C$16,0)-(VLOOKUP(A2,$C$2:$D$16,2,0)="Call Rep"))))

If the output is error this will return 0

 

[xenou]

Index suggests itself to me too. A helper column and some IF's will also work:

<img alt="worksheet" src="http://northernocean.net/etc/mrexcel/20110502_lkp.png" />

Formula in Cell B2 is:
=IF(VLOOKUP(A2,$C$2:$D$10,2,FALSE)="Call Rep",VLOOKUP(A2,$C$2:$E$10,3,FALSE),VLOOKUP(A2,$C$2:$D$10,2,FALSE))
Formula in E3 is simply:
=D2 (we skip D2 as a first row "seed")
So this value provides a "secondary" lookup if the first returns "Call Rep"

But this seems to point out a problem - that the first row has no price in the cell above it. Also two "Call Rep" values in a row will be a problem - as when we look up the 7 in my example.