Search for TEXT in a column and return a value of the next columns

[n0n0n0]

Dear all excel experts,

I have tried to use INDEX and MATCH yet still can't figure this out:


Table 1:
[TABLE="width: 749"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Remark[/TD]
[TD]
Currency Amt[/TD]
[/TR]
[TR]
[TD]Vchr# 100799, Pymt ID# flliqtaxcc0558902062, Supplier: Dept. of Bus. & Pro. Regulation, Cash Acct - A/P Acct[/TD]
[TD="align: right"]67,999.13[/TD]
[/TR]
[TR]
[TD]Vchr# 99059, Pymt ID# 445193, Supplier: Caribbean Distillers, LLC, Cash Acct - A/P Acct[/TD]
[TD="align: right"]65,707.75[/TD]
[/TR]
[TR]
[TD]Vchr# 99051, Pymt ID# w/VinDore1875/bf19, Supplier: VIN DORÉ 24K[/TD]
[TD="align: right"]64,800.00[/TD]
[/TR]
[TR]
[TD]Vchr# 99537, Pymt ID# 445214, Supplier: Caribbean Distillers, LLC, Cash Acct - A/P Acct[/TD]
[TD="align: right"]64,134.50[/TD]
[/TR]
[TR]
[TD]Vchr# 99495, Pymt ID# 445207, Supplier: Sugarlands Distilling Company, Cash Acct - A/P Acct[/TD]
[TD="align: right"]47,340.00[/TD]
[/TR]
[TR]
[TD]Vchr# 102094, Supplier: Sugarlands Distilling Company, A/P Acct - Exp Accts[/TD]
[TD="align: right"]22,371.00[/TD]
[/TR]
[TR]
[TD]Vchr# 99853, Pymt ID# w/royalwine/810111, Supplier: Royal Wine Corporation[/TD]
[TD="align: right"]15,167.50[/TD]
[/TR]
[TR]
[TD]Vchr# 99058, Pymt ID# 445197, Supplier: Sugarlands Distilling Company, Cash Acct - A/P Acct[/TD]
[TD="align: right"]11,700.00[/TD]
[/TR]
[TR]
[TD]Vchr# 99440, Pymt ID# 445199, Supplier: Bridge / SG WORLDWIDE[/TD]
[TD="align: right"]11,460.00[/TD]
[/TR]
</tbody>[/TABLE]


Table 2:

[TABLE="width: 287"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Cargo Import Brokers Inc.[/TD]
[/TR]
[TR]
[TD]Frank-lin Distillers[/TD]
[/TR]
[TR]
[TD]J.F. Hillebrand USA Inc.[/TD]
[/TR]
[TR]
[TD]JF Hillebrand USA[/TD]
[/TR]
[TR]
[TD]M.S. Walker[/TD]
[/TR]
[TR]
[TD]T-MOBILE[/TD]
[/TR]
[TR]
[TD]Aiko Importers Inc[/TD]
[/TR]
[TR]
[TD]PARK STREET IMPORTS / BRICKELL WINES[/TD]
[/TR]
[TR]
[TD]Camrose Trading[/TD]
[/TR]
[TR]
[TD]Command Transportation LLC

I'm trying to search for the name on Table 2 in Column Remark of Table 1, and if there's a match, then return the total value in Column Currency Amount next to Column Name of Table 2.

Thank you very much for your advice and helps


[/TD]
[/TR]
</tbody>[/TABLE]

 

[n0n0n0]

Woah...this is fantastic!

Quick question: If my tables have a large amount of data, meaning instead of A2:A10, I'll have Sheet1!A2:A300, and instead of A15:A25, I'll have Sheet2!A2:A150. Won't this cause excel to take a long time to execute the formula?
If so, is there a formula for large data tables?

Thank you for your explanation! I have learnt so much.

 

[Eric W]

That's a valid question. The answer depends on what operating system you have, what version of Excel, what the specs on your PC are, what you have running in the background, etc. I once wrote a macro for someone in England that ran in about a minute for me, but over 5 hours for him. I could not figure out the difference - very frustrating.

The ranges you describe aren't that big, it's probably worth a try installing the formulas and just seeing how it goes. If there's a noticeable lag, we can possibly have a Worksheet_Change event monitor your ranges, and if there's a change, have the macro do the highlighting. In fact, I've been working on a very similar question: https://www.mrexcel.com/forum/excel-questions/1036556-find-number-range-cell.html

 

[n0n0n0]

Hi Eric,

The formula works flawlessly for what I need right now. My question regarding the execution time is just because of my curiosity. I have checked out your and Rick's works in the thread that you mentioned above, and will take advantage of the macro solution when the time comes.
I really appreciate your time and help. Would you mind give me an advise for where to obtain learning material to improve my Excel skills. My current mediocre skill is currently at Vlookup and Pivot Table.

Again, thank you for everything.

Phong T.

 

[Eric W]

I'm glad the formula works for you.

As far as getting materials to learn with, I'm usually a pretty bad person to ask! Most of my learning came from just playing around with it, a lot of trial and error. That's my usual learning method. Some people prefer books, some prefer videos, some prefer searching the web, some prefer tutors. You need to figure out your learning style. One of our long-time members @hiker95 has compiled an incredible list of Excel resources on the web. You can find a recent list of them here:

https://www.mrexcel.com/forum/lounge-v-2-0/1031751-two-novice-questions.html

One problem with it is that it's almost too big! It can be a bit scary, so just pick one small section, look at it until it makes sense, then try something else. There's also a link to the ExcelIsFun channel on YouTube which some people really like. Come back here if you have questions.

Good luck!

 

[n0n0n0]

Hi Eric,

Thank you for the tips. I learn best with hand-on exercises. I'm sure I can find the best materials in the incredible list of @hiker95.