# Formula Help (Match)

#### How_Do_I

##### Well-known Member
Hope I can explain this…
Column B and C are my data… I’m happy with the formula in Cell F4 downwards although I suspect that there’s an easier way to do this… Column G has my expected results in but I can’t work out the formula I need in Cell G4 downwards…
So B4 and B5 both have WM (39) 4 (they are meeting references) but this meeting produced a confidential Annex indicated in C4.
I want the formula to look in Column C, find the words “Confidential Annex” and give me those returns ignoring the numbers in brackets… I hope I’ve explained that well enough to get some assistance…

Sheet1

 B C D E F G 3 Year Annex Meeting WM (39) Confidential Annex 4 WM (39) 4 (1) Confidential Annex 3 In Collection 5 WM (39) 4 4 In Collection In Collection 6 WM (39) 311 311 In Collection 7 WM (39) 14 (1) (4) Confidential Annex 14 In Collection 8 WM (39) 3

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:117px;"><col style="width:129px;"><col style="width:129px;"><col style="width:62px;"><col style="width:106px;"><col style="width:129px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
 Cell Formula F4 =IF(ISNUMBER(MATCH(CONCATENATE(\$F\$3," ",\$E4),B:B,0)),"In Collection","") G4 =IF(ISNUMBER(MATCH(CONCATENATE(\$F\$3," ",\$E4," ","("),C:C,0)),"In Collection","") F5 =IF(ISNUMBER(MATCH(CONCATENATE(\$F\$3," ",\$E5),B:B,0)),"In Collection","") F6 =IF(ISNUMBER(MATCH(CONCATENATE(\$F\$3," ",\$E6),B:B,0)),"In Collection","") F7 =IF(ISNUMBER(MATCH(CONCATENATE(\$F\$3," ",\$E7),B:B,0)),"In Collection","")

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Last edited:

### Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Hi,

Why is G4 Blank, and G5 "In Collection", when C4 is "Confidential Annex" ?

jtakw… I've confused myself here... I'll look at my Jeanie again and repost...

I'll post this... If you are still unsure then let me know please...

Excel Workbook
BCDEFG
3YearAnnexMeetingWM (39)Confidential Annex
4WM (39) 4 (1)Confidential Annex3In Collection
5WM (39) 44In CollectionIn Collection
6WM (39) 311311In Collection
7WM (39) 14 (1) (4)Confidential Annex14 In Collection
8WM (39) 3
Sheet1

Yes, I'm still confused.

Again, why is G4 Blank, and G5 "In Collection", when C4 is "Confidential Annex" ?
Shouldn't G4 be "In Collection", and G5 Blank ?

If what you post are the expected results, please explain the logic.

I’ve dropped the CONCATENATE and used the meeting numbers…

There’s an issue because later in my workbook some of the bracketed numbers are missing but “Confidential Annex” is always in place…
Cell F4 downwards is working…

G4 I’m trying to asking xl is there anything in Column B that starts WM (39) 3 and only those numbers that has “Confidential Annex” in the cell on the same row in Column C. (No return because B8 has “WM (39) 3 but C8 DOES NOT contain “Confidential Annex”).

G5 I’m trying to ask xl is there anything in Column B that starts WM (39) 4 and only those numbers that has “Confidential Annex” in the cell on the same row in Column C. (Yes, B4 has the search-match and C4 has “Confidential Annex” therefore, “In Collection”).

Sheet1

 B C D E F G 3 Meeting Annex Meeting WM (39) Confidential Annex 4 WM (39) 4 (1) Confidential Annex WM (39) 3 In Collection 5 WM (39) 4 WM (39) 4 In Collection In Collection 6 WM (39) 311 WM (39) 311 In Collection 7 WM (39) 14 (1) (4) Confidential Annex WM (39) 14 In Collection 8 WM (39) 3

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:117px;"><col style="width:129px;"><col style="width:129px;"><col style="width:83px;"><col style="width:106px;"><col style="width:129px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
 Cell Formula F4 =IF(ISNUMBER(MATCH(E4,B:B,0)),"In Collection","") F5 =IF(ISNUMBER(MATCH(E5,B:B,0)),"In Collection","") F6 =IF(ISNUMBER(MATCH(E6,B:B,0)),"In Collection","") F7 =IF(ISNUMBER(MATCH(E7,B:B,0)),"In Collection","")

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

<strike></strike>

<strike></strike>

Last edited:
Thanks for clarifying, think I understand now.

Your F4 formula is fine, I'm just including an alternative for F4 copied down.
G4 formula copied down, let me know if it works for you.

Book1
BCDEFG
3YearAnnexMeetingWM (39)Confidential Annex
4WM (39) 4 (1)Confidential AnnexWM (39) 3In Collection
5WM (39) 4WM (39) 4In CollectionIn Collection
6WM (39) 311WM (39) 311In Collection
7WM (39) 14 (1) (4)Confidential AnnexWM (39) 14In Collection
8WM (39) 3
Sheet603
Cell Formulas
RangeFormula
F4=IF(COUNTIF(B\$4:B\$8,E4),"In Collection","")
G4=IF(E4="","",IF(COUNTIFS(B\$4:B\$8,E4&"*",C\$4:C\$8,"Confidential Annex"),"In Collection",""))

Last edited:
Thank you for your time jtakw... I knew trying to explain what I wanted wasn't going to be easy but I'm sure the solution is working... I noticed B7 was causing an issue, a "Confidential Annex" but no record of the meeting... It shouldn't occur in my data but I have to find the meeting data online and I suppose I could miss something... The reverse of your G4 seems to work... Thanks again for your time...

Excel Workbook
BCDEFG
3MeetingAnnexMeetingWM (39)Confidential Annex
4WM (39) 4Confidential AnnexWM (39) 3In Collection
5WM (39) 4WM (39) 4
6WM (39) 311WM (39) 311
7WM (39) 14Confidential AnnexWM (39) 14
8WM (39) 3
Sheet1

Replies
0
Views
453
Replies
28
Views
524
Replies
0
Views
793
Replies
0
Views
215
Replies
5
Views
261

Threads
1,203,238
Messages
6,054,313
Members
444,715
Latest member
GlitchHawk

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back