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]

If the MATCH function is returning #N/A the the two cells are not an exact match.

Can you post some sample data that shows the problem.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[craigus51286]

If the MATCH function is returning #N/A the the two cells are not an exact match.

Can you post some sample data that shows the problem.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thanks for the quick response.

I am not getting the #N/A error; the formula behaves perfectly fine but just doesn't recognise and give the right result when using 'ABC-1234-71-06' vs 'ABC-1234-71-0' or 'ABC-1234-71-0 6' which is giving the result I am wanting.

I will look into the add in....

 

[Fluff]

If you are not getting the #N/A error, then what are you getting?
Also what is your formula?

 

[craigus51286]

If you are not getting the #N/A error, then what are you getting?
Also what is your formula?

I am getting a result as if the two cells weren't matching.

=IF(ISBLANK(INDEX(Transmittals!AS:AS, MATCH(A5, Transmittals!C:C, 0))), "", IF(ISERROR(IF(AND(ISNUMBER(MATCH(A5, Transmittals!C:C, 0)), INDEX(Transmittals!T:T, MATCH(A5, Transmittals!C:C, 0))="V"), INDEX(Transmittals!AS:AS, MATCH(A5, Transmittals!C:C, 0)), "")), "", IF(AND(ISNUMBER(MATCH(A5, Transmittals!C:C, 0)), INDEX(Transmittals!T:T, MATCH(A5, Transmittals!C:C, 0))="V"), INDEX(Transmittals!AS:AS, MATCH(A5, Transmittals!C:C, 0)), "")))

Using the above, Cell A5 from Sheet 1 needs to match with Column C of sheet 2 (Transmittals) with the condition of 'V' also present. If all true then a value is taken from AS into the cell this formula is.

When I enter into A5 and column C 'ABC-1234-71-06' I get a blank result however 'ABC-1234-71-0' or 'ABC-1234-71-0 6' gives me the result I am actually looking for from 'ABC-1234-71-06'.

 

[craigus51286]

Hopefully I've done this right but sheet 1:

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

Cell Formulas
Range	Formula
D5:D6	D5	=IF(ISBLANK(INDEX(Transmittals!AS:AS, MATCH(A5, Transmittals!C:C, 0))), "", IF(ISERROR(IF(AND(ISNUMBER(MATCH(A5, Transmittals!C:C, 0)), INDEX(Transmittals!T:T, MATCH(A5, Transmittals!C:C, 0))="V"), INDEX(Transmittals!AS:AS, MATCH(A5, Transmittals!C:C, 0)), "")), "", IF(AND(ISNUMBER(MATCH(A5, Transmittals!C:C, 0)), INDEX(Transmittals!T:T, MATCH(A5, Transmittals!C:C, 0))="V"), INDEX(Transmittals!AS:AS, MATCH(A5, Transmittals!C:C, 0)), "")))

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

Sheet 2 (Transmittals):

Project Engineering Transmittal Tracker Plan WIP.xlsx
	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
11	GFC-0306-71-06										D		DRAWING					V	Private LVAC Dist.Boards Arrangement																					08/02/24			4
15	GFC-0306-70-01										F		DRAWING					V	Private Single Line Diagram																					01/05/24			4
Transmittals

 

[Fluff]

Thanks for that, unfortunately your formula works for me & both return 4.
What does this formula return

=A5=Transmittals!C11

 

[craigus51286]

Thanks for that, unfortunately your formula works for me & both return 4.
What does this formula return

=A5=Transmittals!C11

Project Engineering Transmittal Tracker Plan WIP.xlsx
	A	B	C	D	E	F	G
5	GFC-0306-71-06	Private LVAC Dist.Boards Arrangement	No
6	GFC-0306-70-01	Private Single Line Diagram	No				TRUE
Master Doc List

Cell Formulas
Range	Formula
D5:D6	D5	=IF(ISBLANK(INDEX(Transmittals!AS:AS, MATCH(A5, Transmittals!C:C, 0))), "", IF(ISERROR(IF(AND(ISNUMBER(MATCH(A5, Transmittals!C:C, 0)), INDEX(Transmittals!T:T, MATCH(A5, Transmittals!C:C, 0))="V"), INDEX(Transmittals!AS:AS, MATCH(A5, Transmittals!C:C, 0)), "")), "", IF(AND(ISNUMBER(MATCH(A5, Transmittals!C:C, 0)), INDEX(Transmittals!T:T, MATCH(A5, Transmittals!C:C, 0))="V"), INDEX(Transmittals!AS:AS, MATCH(A5, Transmittals!C:C, 0)), "")))
E5:E6	E5	=IF(ISBLANK(INDEX(Transmittals!AP:AP, MATCH(A5, Transmittals!C:C, 0))), "", IF(ISERROR(IF(AND(ISNUMBER(MATCH(A5, Transmittals!C:C, 0)), INDEX(Transmittals!T:T, MATCH(A5, Transmittals!C:C, 0))="V"), INDEX(Transmittals!AP:AP, MATCH(A5, Transmittals!C:C, 0)), "")), "", IF(AND(ISNUMBER(MATCH(A5, Transmittals!C:C, 0)), INDEX(Transmittals!T:T, MATCH(A5, Transmittals!C:C, 0))="V"), INDEX(Transmittals!AP:AP, MATCH(A5, Transmittals!C:C, 0)), "")))
F5:F6	F5	=IF(ISBLANK(INDEX(Transmittals!BF:BF, MATCH(A5, Transmittals!C:C, 0))), "", IF(ISERROR(IF(AND(ISNUMBER(MATCH(A5, Transmittals!C:C, 0)), INDEX(Transmittals!T:T, MATCH(A5, Transmittals!C:C, 0))="V"), INDEX(Transmittals!BF:BF, MATCH(A5, Transmittals!C:C, 0)), "")), "", IF(AND(ISNUMBER(MATCH(A5, Transmittals!C:C, 0)), INDEX(Transmittals!T:T, MATCH(A5, Transmittals!C:C, 0))="V"), INDEX(Transmittals!BF:BF, MATCH(A5, Transmittals!C:C, 0)), "")))
G6	G6	=A5=Transmittals!C11

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

I only get '4' in cell D5 when I change 'GFC-0306-71-06' in A5 and C11 to either 'GFC-0306-71-0 6' or 'GFC-0306-71-0'

I'm completely baffled.

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

Cell Formulas
Range	Formula
D5:D6	D5	=IF(ISBLANK(INDEX(Transmittals!AS:AS, MATCH(A5, Transmittals!C:C, 0))), "", IF(ISERROR(IF(AND(ISNUMBER(MATCH(A5, Transmittals!C:C, 0)), INDEX(Transmittals!T:T, MATCH(A5, Transmittals!C:C, 0))="V"), INDEX(Transmittals!AS:AS, MATCH(A5, Transmittals!C:C, 0)), "")), "", IF(AND(ISNUMBER(MATCH(A5, Transmittals!C:C, 0)), INDEX(Transmittals!T:T, MATCH(A5, Transmittals!C:C, 0))="V"), INDEX(Transmittals!AS:AS, MATCH(A5, Transmittals!C:C, 0)), "")))
E5:E6	E5	=IF(ISBLANK(INDEX(Transmittals!AP:AP, MATCH(A5, Transmittals!C:C, 0))), "", IF(ISERROR(IF(AND(ISNUMBER(MATCH(A5, Transmittals!C:C, 0)), INDEX(Transmittals!T:T, MATCH(A5, Transmittals!C:C, 0))="V"), INDEX(Transmittals!AP:AP, MATCH(A5, Transmittals!C:C, 0)), "")), "", IF(AND(ISNUMBER(MATCH(A5, Transmittals!C:C, 0)), INDEX(Transmittals!T:T, MATCH(A5, Transmittals!C:C, 0))="V"), INDEX(Transmittals!AP:AP, MATCH(A5, Transmittals!C:C, 0)), "")))
F5:F6	F5	=IF(ISBLANK(INDEX(Transmittals!BF:BF, MATCH(A5, Transmittals!C:C, 0))), "", IF(ISERROR(IF(AND(ISNUMBER(MATCH(A5, Transmittals!C:C, 0)), INDEX(Transmittals!T:T, MATCH(A5, Transmittals!C:C, 0))="V"), INDEX(Transmittals!BF:BF, MATCH(A5, Transmittals!C:C, 0)), "")), "", IF(AND(ISNUMBER(MATCH(A5, Transmittals!C:C, 0)), INDEX(Transmittals!T:T, MATCH(A5, Transmittals!C:C, 0))="V"), INDEX(Transmittals!BF:BF, MATCH(A5, Transmittals!C:C, 0)), "")))
G6	G6	=A5=Transmittals!C11

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

 

[Fluff]

Ok, with the values that don't work what does this return

=MATCH(A5, Transmittals!C:C, 0)

 

[craigus51286]

Ok, with the values that don't work what does this return

=MATCH(A5, Transmittals!C:C, 0)