MATCH function not MATCHING

[craigus51286]

Hi All,

First time post but I am completely at a loss where I have decided to ask the internet for help.

I have two sheets I am working with and want to populate a cell depending on some conditions. E.g. If cell A1 of sheet 1 matches cell B2 of sheet two then cell B1 of sheet 1 will populate itself by copying cell D1 of sheet 2.

I have a much larger formula I am working with but came across an issue that I have whittled down to what I believe is the MATCH function not behaving. My formula works perfectly when trying to match the following as an example, 'ABC-1234-71-0' or 'ABC-1234-71-0 6' however, fails on 'ABC-1234-71-06' which is what I want the formula to match on.

I've spent hours checking everything I could possibly think of. They are an absolute exact match but its just not being seen.

Maybe I am being stupid or I have missed something glaring. With any luck, someone has had this happen to them and can help......

 

[Fluff]

In that case the value in A5 exists in more than one row on the Transmittals sheet.
And Either T8 does not ="V" or AS8 is blank.

 

[craigus51286]

In that case the value in A5 exists in more than one row on the Transmittals sheet.
And Either T8 does not ="V" or AS8 is blank.

Correct, the value in A8 exists in multiple rows with only one of these with the V in T8. It appears the formula is not picking up the V so isn't able to differentiate between them so defaults to a blank cell.

 

[Fluff]

Match will only find the 1st value that matches.

 

[Fluff]

If only one row will have the V in col T try

=FILTER(Transmittals!AS:AS,(Transmittals!C:C=A5)*(Transmittals!T:T="V"),"")

 

[craigus51286]

If only one row will have the V in col T try

=FILTER(Transmittals!AS:AS,(Transmittals!C:C=A5)*(Transmittals!T:T="V"),"")

I couldn't get FILTER to work, maybe my excel version was stopping it. You did highlight my oversight with the MATCH function so thank you so much for that.

I have tried a different approach using LOOKUP which seems to have done the trick or at least in my testing it hasn't gone haywire....

Project Engineering Transmittal Tracker Plan WIP.xlsx
	A	B	C	D	E	F
4	Document Number	Document Title	Received?	Latest Transmittal No.	Latest Transmittal Received Date	Latest Transmittal Returned Date
5	GFC-0306-71-06	Private LVAC Dist.Boards Arrangement	No	5	03/05/2024
6	GFC-0306-70-01	Private Single Line Diagram	No	4	01/05/2024	03/05/2024
Master Doc List

Cell Formulas
Range	Formula
D5:D6	D5	=IFERROR(IF(LOOKUP(2,1/(Transmittals!C:C=A5)/(Transmittals!T:T="V"), Transmittals!AS:AS)="","",LOOKUP(2,1/(Transmittals!C:C=A5)/(Transmittals!T:T="V"), Transmittals!AS:AS)), "")
E5:E6	E5	=IFERROR(IF(LOOKUP(2,1/(Transmittals!C:C=A5)/(Transmittals!T:T="V"), Transmittals!AP:AP)="","",LOOKUP(2,1/(Transmittals!C:C=A5)/(Transmittals!T:T="V"), Transmittals!AP:AP)), "")
F5:F6	F5	=IFERROR(IF(LOOKUP(2,1/(Transmittals!C:C=A5)/(Transmittals!T:T="V"), Transmittals!BF:BF)="","",LOOKUP(2,1/(Transmittals!C:C=A5)/(Transmittals!T:T="V"), Transmittals!BF:BF)), "")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C5:C1048576	Cell Value	contains "No"	text	NO
C5:C1048576	Cell Value	contains "Yes"	text	NO
C5:C96	Cell Value	contains "Y"	text	NO
C5:C96	Cell Value	contains "N"	text	NO
A5:A96	Cell Value	="please do not use this line - insert one above"	text	NO
A5:A96	Expression	=#REF!="CLOSED"	text	NO

Project Engineering Transmittal Tracker Plan WIP.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU
7	Document Number												Revision		Document Type					Revision Status	Document Title																					Received Date			Transmittal Ref. No.				Issue Purpose		Remarks							Returned Date			Transmittal Ref. N.				Review Status		Remarks
8			GFC-0306-71-06										A		DRAWING					P	Private LVAC Dist.Boards Arrangement																					02/11/23			1
9			GFC-0306-71-06										B		DRAWING					S	Private LVAC Dist.Boards Arrangement																					16/11/23			2
10			GFC-0306-71-06										C		DRAWING					S	Private LVAC Dist.Boards Arrangement																					02/01/24			3
11			GFC-0306-71-06										D		DRAWING					S	Private LVAC Dist.Boards Arrangement																					08/02/24			4													08/03/24
12			GFC-0306-70-01										A		DRAWING					S	Private Single Line Diagram																					08/02/24			1
13			GFC-0306-70-01										D		DRAWING					S	Private Single Line Diagram																					06/02/24			2
14			GFC-0306-70-01										E		DRAWING					S	Private Single Line Diagram																					01/05/24			3
15			GFC-0306-70-01										F		DRAWING					V	Private Single Line Diagram																					01/05/24			4													03/05/24
16			GFC-0306-71-06										E		DRAWING					V	Private LVAC Dist.Boards Arrangement																					03/05/24			5
Transmittals

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
BM:BN	Cell Value	="RI"	text	NO
BM:BN	Cell Value	="NR"	text	NO
BM:BN	Cell Value	="RC"	text	NO
BM:BN	Cell Value	="R"	text	NO

 

[Fluff]

Glad you sorted it & thanks for letting us know.