Formula help for text string data

[mwhite17]

Hi,

Please could I get some help for this formula?

I have this formula that matches a text cell, to a text range, and then provides the value of a new cell. Like below.

=INDEX($B$2:$B$6,MATCH(1,COUNTIF(C4,"*"&$A$2:$A$6&"*"),0))

A B C D
Idea Category Rating Respondents Ideas Respondents Rating
Ship 1 Heart 4
Boat 2 Love 3
Love 3 Boat 2
Heart 4 Ship 1
(formula column)

If possible, I would like the formula to match any cell in column C, which contains the string in column A. At the moment it is only working for direct matches between cells but I have some cells that have other words, such as 'Big Boat', 'Sailing Ship, but I want them to be picked up by boat and ship respectively.

Thanks,
Matt

 

[mwhite17]

Actually, sorry scratch that I think this already does what I want, sorry!

 

[Peter_SSs]

I think this already does what I want

Are you sure? If column A contains "Love" and column C contains "Slovenly" your formula counts that as a match. Is that what you want?

 

[Peter_SSs]

If you wanted to match whole words only, you could try this in D2, copied down.

=LOOKUP(9.99E+307,SEARCH(" "&A2&" "," "&$C$2:$C$6&" "),$B$2:$B$6)

 

[mwhite17]

If you wanted to match whole words only, you could try this in D2, copied down.

=LOOKUP(9.99E+307,SEARCH(" "&A2&" "," "&$C$2:$C$6&" "),$B$2:$B$6)

Hi Peter,

Thanks for your help. Yes I would only like to match individual words. I've tried your formula in my sheet but I'm getting an N/A error.

Thanks,
Matt

 

[Peter_SSs]

I've tried your formula in my sheet but I'm getting an N/A error.

That should only occur if there is no match (& you haven't said what you want to happen if that occurs).
Here is my sheet. Formula in D2 is copied down.
Do I have the setup wrong?

Excel Workbook
	A	B	C	D
1	Idea Category	Rating	Respondents Ideas	Respondents Rating
2	Ship	1	Heart	4
3	Boat	2	Love	3
4	Love	3	Big Boat	2
5	Heart	4	Sailing Ship	1
6	Bus	5	Big Business	#N/A
mwhite17

 

[mwhite17]

That should only occur if there is no match (& you haven't said what you want to happen if that occurs).
Here is my sheet. Formula in D2 is copied down.
Do I have the setup wrong?

mwhite17

	A	B	C	D
Idea Category	Respondents Ideas
Ship	Heart
Boat	Love
Love	Big Boat
Heart	Sailing Ship
Bus	Big Business	#N/A

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:100px;"><col style="width:56px;"><col style="width:132px;"><col style="width:138px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: center"]Rating[/TD]

[TD="align: center"]Respondents Rating[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: center"]5[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 =LOOKUP(9.99E+307,SEARCH(" "&A2&" "," "&$C$2:$C$6&" "),$B$2:$B$6) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Column A is made of of common text strings from Column C, so I shouldn't get an #N/As.

I've got your formula working, but it doesn't seem to work when there are duplicate values in Column C.

If I put input 'Heart' in C6, it changes the rating of D5 and D6 remains as #N/A.

Thanks

 

[Peter_SSs]

Sorry, my formula was quite flawed. Try this instead. If this still doesn't do what you expect, please provide some sample data (say 8-10 rows) together with the expected results and any further clarification that you can give.

Excel Workbook
	A	B	C	D
1	Idea Category	Rating	Respondents Ideas	Respondents Rating
2	Ship	1	Heart	4
3	Boat	2	Tractor	#N/A
4	Love	3	Big Boat	2
5	Heart	4	Sailing Ship	1
6	Bus	5	Broken Heart	4
mwhite17