help with impossible formula

T

taxi driver

New member
Joined
Aug 6, 2011
Messages
3
Reaction score
0
Points
0
please help! here are the two formulas i would like to combine. both work standing alone, but i need them both to be in the same cell if that is even possible.

=SUMIF(Sheet2!B$2:B$20,"="&A18,Sheet2!D2:D20)

=LOOKUP(2,1/(D2:D20<>""),D2:D20)

further explanation, (see attached workbook) when AMD is entered into A18 the result in cell d18 should be 5.00. when a ticker is entered that matches an entry in sheet2 then the latest data entry from sheet2 cell range d2:d20 should be pulled into the appropriate d column cell in sheet1. sheet2 is continually being added to i do not want a SUM, but only the latest corresponding price.
Thanks in advance if anyone can help
 

Attachments

  • Book2.xlsx
    9.1 KB · Views: 16
I've not looked at your example yet but when you say you want them both in the same cell what do you mean, do you want the result of the SUM then the result of the vlookup or do they need to calculate with each other?
 
they need to calculate with each other. i am not looking for a SUM so the first formula is obviously wrong. that's why i assume a LOOKUP formula is also needed to refine the results. i want to enter a ticker in sheet1 that matches an entry in sheet2 then the latest price entry from sheet2 cell range d2:d20 should be pulled into the appropriate d column cell in sheet1. sheet2 is continually being added to
 
in C18 =VLOOKUP(Sheet1!A18,Sheet2!B6:D11,2,FALSE)
In D18 =VLOOKUP(Sheet1!A18,Sheet2!B6:D11,3,FALSE)

Then copy down!
 
Ah!, just noticed a problem, you have two AMD's VLookup will always return the first result, how will you be able to distinguish between teh two AMD results and which one you woill want?
 
Solved

THANKS to Simon Lloyd for your help and replies! i received this formula from another forum that did the trick.


=LOOKUP(2,1/(Sheet2!$B$1:$B$1000=Sheet1!A18),Sheet2!$D$1:$D$1000)

Thanks for spending the time to help.
 
You must log in or register to reply here.
Back
Top