Formula Help (Match)

[How_Do_I]

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

 

[jtakw]

Hi,

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

 

[How_Do_I]

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

 

[How_Do_I]

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

Excel Workbook
	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
Sheet1

 

[jtakw]

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.

 

[How_Do_I]

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>

 

[jtakw]

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
	B	C	D	E	F	G
3	Year	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
Sheet603

Cell Formulas
Range	Formula
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",""))

 

[How_Do_I]

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
	B	C	D	E	F	G
3	Meeting	Annex		Meeting	WM (39)	Confidential Annex
4	WM (39) 4	Confidential Annex		WM (39) 3	In Collection
5	WM (39) 4			WM (39) 4
6	WM (39) 311			WM (39) 311
7	WM (39) 14	Confidential Annex		WM (39) 14
8	WM (39) 3
Sheet1