help with impossible formula

[taxi driver]

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

[Simon Lloyd]

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?

[taxi driver]

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

[Simon Lloyd]

in C18 =VLOOKUP(Sheet1!A18,Sheet2!B6:D11,2,FALSE)
In D18 =VLOOKUP(Sheet1!A18,Sheet2!B6:D11,3,FALSE)

Then copy down!

[Simon Lloyd]

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?

[taxi driver]

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.

[Ken Puls]

Next time, please post the link to the other forum at the beginning of the process. Here's why: http://www.excelguru.ca/forums/faq.php?faq=crossposting