# Thread: help with impossible formula

1. ## help with impossible formula

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!D220)

=LOOKUP(2,1/(D220<>""),D220)

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 toi do not want a SUM, but only the latest corresponding price.
Thanks in advance if anyone can help

2. 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?

3. 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

4. in C18 =VLOOKUP(Sheet1!A18,Sheet2!B611,2,FALSE)
In D18 =VLOOKUP(Sheet1!A18,Sheet2!B611,3,FALSE)

Then copy down!

5. 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?

6. ## 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.

7. 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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•