Match multiple numbers in a Column against multiple strings and return a third Col value

**elyoe011** · 07-17-2015, 02:58 PM

I'm trying to match/search the numbers in Col A:A (not in order) against the strings in B:B (in order) and if number exist, return the value in C:C. I'm trying to put this formula in column D.

A: B: C: D:
1 25489 Frank - 15655 - Stuff 145
2 32587 Julia - 66556 - Plane 258
3 22477 Jeff - 12555 - This 336
4 Prieto - 25489 - That 325
5 21486 - 2156
6 12555 - 155
7 15655 Jay - 32587 - Hey 2125
8 66556 555555

Example1: If number (15655 - A7) exist in any string in Col B (in this case it exist in B1 - Frank - 15655 - Stuff) return the value in Col C (in this case is 145)

Example2: Number (12555 - A6) is in B3 (Jeff - 12555 - This ), return value in C3 (336), and so on.......

Thanks in advance!!

**daffodil11** · 07-17-2015, 03:11 PM

Perhaps:

=IFERROR(LOOKUP(2,1/ISNUMBER(SEARCH(A1,$B$1:$B$8)),$C$1:$C$8),"")

**FlameRetired** · 07-17-2015, 03:28 PM

Or this one:

Formula:

Please Login or Register  to view this content.

Produces different results in the last 5 rows.

Edit:
This takes care of the 0's in the last 5 rows of the above:

Formula:

Please Login or Register  to view this content.

**elyoe011** · 07-17-2015, 03:47 PM

They both worked!!!!!! Now... Can I use the same formula if the values are in different workbooks? How about if I have multiple strings that contain the same number, in other words, multiple matches....?

**FlameRetired** · 07-17-2015, 04:43 PM

Probably, but could you upload a sample?

**elyoe011** · 07-17-2015, 05:00 PM

Different workbooks: I did it whit the following code:
=IFERROR(LOOKUP(3, 2/ISNUMBER(SEARCH(B6, Report07172015.csv!$C$2:$C$485)), Report07172015.csv!$B$2:$B$485), "")

Now... My questions is: What happens when it finds multiple strings that contains the same number?

**FlameRetired** · 07-17-2015, 08:14 PM

Try this variation:

Formula:

Please Login or Register  to view this content.

File is attached.

**elyoe011** · 07-20-2015, 09:04 AM

That works too! But... What I really want to do is.... Like in your workbook (match-multiple-numbers-in-a-column-against-multiple-strings-and-return-a-third-col-value-match-multiple-numbers-in-a-column-against-multip.xlsx), since B2 (Julia - 66556 - Plane) and B15 (Julia - 66556 - Plane) numbers (66556) match. I would like to combine the result into one cell, for example: E2 would look like (258, 1776)

**FlameRetired** · 07-20-2015, 05:45 PM

Originally Posted by elyoe011

I would like to combine the result into one cell, for example: E2 would look like (258, 1776)

OK. Then what would cells E12 and E15 look like?

Edit: Answered my own question.

**newdoverman** · 07-20-2015, 06:06 PM

I used this formula for the first problem:

Formula:

Please Login or Register  to view this content.

Will look at the second part.

**FlameRetired** · 07-20-2015, 06:29 PM

elyoe011,

Concatenating two rows of output from the same column is going to take me some time to figure out. This could take me a while.

**newdoverman** · 07-20-2015, 07:00 PM

This might work. It combines daffodill11's formula with mine using helper columns:
Find First occurrence enter in D2 and fill down:

Formula:

Please Login or Register  to view this content.

Find last occurrence enter in E2 and fill down:

Formula:

Please Login or Register  to view this content.

Enter in F2 and fill down:

Formula:

Please Login or Register  to view this content.

Result:

	A	B	C	D	E	F
1				First	Last	Combo
2	25489	Frank - 15655 - Stuff	145	325	325	325
3	32587	Julia - 66556 - Plane	258	2125	2125	2125
4	22477	Jeff - 12555 - This	336
5	33555	Prieto - 25489 - That	325
6	21486	-	2156
7	12555	-	155	336	336	336
8	15655	Jay - 32587 - Hey	2125	145	7	145, 7
9	66556	1	555555	258	6	258, 6
10		Prieto - 2222 - That	1		7
11		Prieto - 2233656 - That	2		7
12		Prieto - 366 - That	3		7
13		Prieto - 5455 - That	4		7
14		Prieto - 566 - That	5		7
15		Julia - 66556 - Plane	6		7
16		Frank - 15655 - Stuff	7		7

**FlameRetired** · 07-20-2015, 08:03 PM

I used a User Defined Function that circulates on the Forum call Concatall (see code below). It is written by tigeravatar. I used it wrapped around this array-entered formula in cell F1 in the attached and filled down:

Formula:

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

If you are not familiar with how to use UDFs copy the below code, in Excel open the VBA editor (Alt + F11), click on Edit in the menu and Insert Module. Then paste this code into that module.

By the way: The formula above does not include the Personal.xlsb! prefix to Concatall that the upload has. That is because I store my UDFs / macros in that macro file. If you do not store yours in that file you’ll need to delete that prefix from formula in the upload. I did not include the UDF in this file. I am a bit squeamish about downloading .xlsm files. I suspect others are, too.

Please Login or Register  to view this content.

Does this do what you want?

**FlameRetired** · 07-20-2015, 08:09 PM

BTW:

The file output (column F) looks like this next to the old. I added another row of data to columns B and C to test this.

Row\Col	A	B	C	D	E	F
1	25489	Frank - 15655 - Stuff	145		145	145
2	32587	Julia - 66556 - Plane	258		258	258, 1776
3	22477	Jeff - 12555 - This	336		336	336, 2001
4	33555	Prieto - 25489 - That	325		325	0, 325
5	21486	-	2156			155, 2156
6	12555	-	155			155
7	15655	Jay - 32587 - Hey	2125		2125	2125
8	66556	1	555555			555555
9		Prieto - 2222 - That				0
10		Prieto - 2233656 - That				0
11		Prieto - 366 - That				0
12		Prieto - 25489 - That			0	0
13		Prieto - 5455 - That				0
14		Prieto - 566 - That				0
15		Julia - 66556 - Plane	1776		1776	1776
16		Jeff - 12555 - This	2001		2001	2001

**elyoe011** · 07-21-2015, 11:06 AM

Newdoverman, FlameRetired - Thanks al lot for taking the time to work on this matter..... Both of your suggestions worked great!!! I really appreciate your time a collaboration. elyoe011

**newdoverman** · 07-21-2015, 11:08 AM

Thanks for the feedback. It makes it worthwhile knowing that you have a solution that works for you.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**FlameRetired** · 07-21-2015, 12:56 PM

Echo newdoverman.

Match multiple numbers in a Column against multiple strings and return a third Col value

LinkBack

Thread Tools

Rate This Thread

Display

Match multiple numbers in a Column against multiple strings and return a third Col value

Re: Match multiple numbers in a Column against multiple strings and return a third Col val

Re: Match multiple numbers in a Column against multiple strings and return a third Col val

Re: Match multiple numbers in a Column against multiple strings and return a third Col val

Re: Match multiple numbers in a Column against multiple strings and return a third Col val

Re: Match multiple numbers in a Column against multiple strings and return a third Col val

Re: Match multiple numbers in a Column against multiple strings and return a third Col val

Re: Match multiple numbers in a Column against multiple strings and return a third Col val

Re: Match multiple numbers in a Column against multiple strings and return a third Col val

Re: Match multiple numbers in a Column against multiple strings and return a third Col val

Re: Match multiple numbers in a Column against multiple strings and return a third Col val

Re: Match multiple numbers in a Column against multiple strings and return a third Col val

Re: Match multiple numbers in a Column against multiple strings and return a third Col val

Re: Match multiple numbers in a Column against multiple strings and return a third Col val

Re: Match multiple numbers in a Column against multiple strings and return a third Col val

Re: Match multiple numbers in a Column against multiple strings and return a third Col val

Re: Match multiple numbers in a Column against multiple strings and return a third Col val

Thread Information

Users Browsing this Thread

Similar Threads

Multiple Criteria Index/Match Function in VBA Looking Up Dates/Strings/Numbers

MATCH using Multiple Criteria and Return the Row Numbers

excel formula to search Multiple strings in several columns and return strings

excel formula to search Multiple strings in several columns and return strings in a separa

Match multiple repeating strings in a column and output the row number

text within multiple strings, look @ dates in next column, return most recent date

[SOLVED] Match Multiple columns across multiple sheets return TRUE if match found

MATCH similar strings and return adjacent column value

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions