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]

 

[Scott Huish]

Assuming your table 1 is in A1:B10

And I have table 2 in A15:A25, I added 1 line so there would be a match.
In B15:
=IFERROR(VLOOKUP("*"&A15&"*",$A$2:$B$10,2,0),"")
Copy down.

Excel 2010
	A	B
1	Remark	Currency Amt
2	Vchr# 100799, Pymt ID# flliqtaxcc0558902062, Supplier: Dept. of Bus. & Pro. Regulation, Cash Acct - A/P Acct	67,999.13
3	Vchr# 99059, Pymt ID# 445193, Supplier: Caribbean Distillers, LLC, Cash Acct - A/P Acct	65,707.75
4	Vchr# 99051, Pymt ID# w/VinDore1875/bf19, Supplier: VIN DOR 24K	64,800.00
5	Vchr# 99537, Pymt ID# 445214, Supplier: Caribbean Distillers, LLC, Cash Acct - A/P Acct	64,134.50
6	Vchr# 99495, Pymt ID# 445207, Supplier: Sugarlands Distilling Company, Cash Acct - A/P Acct	47,340.00
7	Vchr# 102094, Supplier: Sugarlands Distilling Company, A/P Acct - Exp Accts	22,371.00
8	Vchr# 99853, Pymt ID# w/royalwine/810111, Supplier: Royal Wine Corporation	15,167.50
9	Vchr# 99058, Pymt ID# 445197, Supplier: Sugarlands Distilling Company, Cash Acct - A/P Acct	11,700.00
10	Vchr# 99440, Pymt ID# 445199, Supplier: Bridge / SG WORLDWIDE	11,460.00
11
12
13
14
15	Cargo Import Brokers Inc.
16	Frank-lin Distillers
17	J.F. Hillebrand USA Inc.
18	JF Hillebrand USA
19	M.S. Walker
20	T-MOBILE
21	Aiko Importers Inc
22	PARK STREET IMPORTS / BRICKELL WINES
23	Camrose Trading
24	Command Transportation LLC
25	Sugarlands Distilling Company	47340.00
Sheet1

Cell Formulas
Range	Formula
B15	=IFERROR(VLOOKUP("*"&A15&"*",$A$2:$B$10,2,0),"")
B16	=IFERROR(VLOOKUP("*"&A16&"*",$A$2:$B$10,2,0),"")
B17	=IFERROR(VLOOKUP("*"&A17&"*",$A$2:$B$10,2,0),"")
B18	=IFERROR(VLOOKUP("*"&A18&"*",$A$2:$B$10,2,0),"")
B19	=IFERROR(VLOOKUP("*"&A19&"*",$A$2:$B$10,2,0),"")
B20	=IFERROR(VLOOKUP("*"&A20&"*",$A$2:$B$10,2,0),"")
B21	=IFERROR(VLOOKUP("*"&A21&"*",$A$2:$B$10,2,0),"")
B22	=IFERROR(VLOOKUP("*"&A22&"*",$A$2:$B$10,2,0),"")
B23	=IFERROR(VLOOKUP("*"&A23&"*",$A$2:$B$10,2,0),"")
B24	=IFERROR(VLOOKUP("*"&A24&"*",$A$2:$B$10,2,0),"")
B25	=IFERROR(VLOOKUP("*"&A25&"*",$A$2:$B$10,2,0),"")

 

[n0n0n0]

Thank you for your quick response ans solution. It works well, except for one detail that I forgot to add in the original post.
If you notice that in some name will have more than one result, i.e "Sugarlands Distilling Company", and in that case, won't the formula only return the 1st result instead of all results?
Is there a way to return the total of all results?

Thank you very much for your help.

 

[Scott Huish]

In B15:
=SUM(IF(ISNUMBER(SEARCH(A15,$A$2:$A$10)),$B$2:$B$10))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.
Copy down.

Excel 2010
	A	B
1	Remark	Currency Amt
2	Vchr# 100799, Pymt ID# flliqtaxcc0558902062, Supplier: Dept. of Bus. & Pro. Regulation, Cash Acct - A/P Acct	67,999.13
3	Vchr# 99059, Pymt ID# 445193, Supplier: Caribbean Distillers, LLC, Cash Acct - A/P Acct	65,707.75
4	Vchr# 99051, Pymt ID# w/VinDore1875/bf19, Supplier: VIN DOR 24K	64,800.00
5	Vchr# 99537, Pymt ID# 445214, Supplier: Caribbean Distillers, LLC, Cash Acct - A/P Acct	64,134.50
6	Vchr# 99495, Pymt ID# 445207, Supplier: Sugarlands Distilling Company, Cash Acct - A/P Acct	47,340.00
7	Vchr# 102094, Supplier: Sugarlands Distilling Company, A/P Acct - Exp Accts	22,371.00
8	Vchr# 99853, Pymt ID# w/royalwine/810111, Supplier: Royal Wine Corporation	15,167.50
9	Vchr# 99058, Pymt ID# 445197, Supplier: Sugarlands Distilling Company, Cash Acct - A/P Acct	11,700.00
10	Vchr# 99440, Pymt ID# 445199, Supplier: Bridge / SG WORLDWIDE	11,460.00
11
12
13
14
15	Cargo Import Brokers Inc.	0.00
16	Frank-lin Distillers	0.00
17	J.F. Hillebrand USA Inc.	0.00
18	JF Hillebrand USA	0.00
19	M.S. Walker	0.00
20	T-MOBILE	0.00
21	Aiko Importers Inc	0.00
22	PARK STREET IMPORTS / BRICKELL WINES	0.00
23	Camrose Trading	0.00
24	Command Transportation LLC	0.00
25	Sugarlands Distilling Company	81,411.00
Sheet1

Cell Formulas
Range	Formula
B15	{=SUM(IF(ISNUMBER(SEARCH(A15,$A$2:$A$10)),$B$2:$B$10))}
B16	{=SUM(IF(ISNUMBER(SEARCH(A16,$A$2:$A$10)),$B$2:$B$10))}
B17	{=SUM(IF(ISNUMBER(SEARCH(A17,$A$2:$A$10)),$B$2:$B$10))}
B18	{=SUM(IF(ISNUMBER(SEARCH(A18,$A$2:$A$10)),$B$2:$B$10))}
B19	{=SUM(IF(ISNUMBER(SEARCH(A19,$A$2:$A$10)),$B$2:$B$10))}
B20	{=SUM(IF(ISNUMBER(SEARCH(A20,$A$2:$A$10)),$B$2:$B$10))}
B21	{=SUM(IF(ISNUMBER(SEARCH(A21,$A$2:$A$10)),$B$2:$B$10))}
B22	{=SUM(IF(ISNUMBER(SEARCH(A22,$A$2:$A$10)),$B$2:$B$10))}
B23	{=SUM(IF(ISNUMBER(SEARCH(A23,$A$2:$A$10)),$B$2:$B$10))}
B24	{=SUM(IF(ISNUMBER(SEARCH(A24,$A$2:$A$10)),$B$2:$B$10))}
B25	{=SUM(IF(ISNUMBER(SEARCH(A25,$A$2:$A$10)),$B$2:$B$10))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[n0n0n0]

Thank you very much. It works like a charm.

I didn't understand the formula 100%. If you have time, would you please explain it for me so I'll be able to apply the concept in the future?

Thank you.

 

[Eric W]

A SUMIF should also work:

B15: =SUMIF($A$2:$A$10,"*"&A15&"*",$B$2:$B$10)

No CSE needed. This works by putting wildcards around the name from table 2.

 

[n0n0n0]

Oh yeah!!! That does make sense now. Thank you, Eric!

Quick question. Let's use the same example above.
If I want to do conditional formatting to highlight rows of A2:A10 that contains the text of A15:A25.
For example, Row A6, A7, A9 would be highlighted while other won't be because none of A15:A25 are in A2:A10, except for Sugarlands Distilling Company.

Thank you for your help and wisdom.

 

[Eric W]

Sure, select A2:B10. Click Conditional Formatting > New Rule > Use a Formula > and enter:

=LOOKUP(2^16,FIND($A$15:$A$25,$A2))

and select your fill color.

Glad we could help!

 

[n0n0n0]

Thank you again for your help!

Would you mind explain to me the purpose of 2^16 in the LOOKUP function, please? I would like to understand to be able to make adjustment if necessary.

Thank you.

 

[Eric W]

Sure. The FIND looks for each of the values in A15:A25 within the value of A2. It returns the location within A2 for each phrase. If it's not found, it returns #VALUE ! So for A2, none of A15:A25 is found, so you get the array {#VALUE !, #VALUE !, ...}. If we're looking at cell A6, the array would be: {#VALUE !, #VALUE !, ... #VALUE !, 41}, because Sugarlands Distilling Company (A25) is found in position 41 of A6.

Now LOOKUP will scan through the array looking for 2^16. The maximum number of characters allowed in a cell is 2^15. So if I look for 2^16, I'll never find it. But another feature of LOOKUP is that if it can't find the number you're looking for (and we've rigged it so it won't), it will return the last number in the array (ignoring errors), or 41.

So on the 2 examples, LOOKUP won't find any matches for A2, so it returns #N/A. On the second example, it finds 41. And finally, the way Conditional Formatting works, if the result of a formula is 0 or an error, it does not trigger the formatting. If the result of the formula is non-zero, like the 41, it triggers the formatting.

One other thought is that you might want to use SEARCH instead of FIND, since SEARCH isn't case-sensitive.

Hope this helps!