hi kdsanderson, welcome to the forum. there are no formulas to recognise colours, but thankfully you pointed out the reason for the colours. see if the file in sheet2 helps. it is an array formula. that means you cannot key in & press ENTER. you must press CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
and i purposely left column M with some blanks. if you have such a scenario & dont want sheet2 to show as 0 (it will return 0 if it finds a match but cell is empty), then format the cells like the screenshot. whatever format it has, it must have 2 semi-colons. fyi, the semi-colons are for separate parts to treat +ve numbers, -ve numbers, blanks. so i want the blanks as nothing.
Bookmarks