Lookup with sumproduct

[chris0b]

Hi! I have tried to find an answer to this on the forum but I'm not entirely sure what I need so I apologise if this has been posted already.

I have a formula which looks up the left 3 characters of a cell against a table containing 3 letter codes, if a certain number is used in the adjacent cell - and returns TRUE if there is a match
However, in my new table there are now codes that are between 3 and 7 digits and my formula only matches against the 3 letter codes and I'm not sure how to change my formula

This is my current formula
=IF(SUMPRODUCT(--('[JV Data.XLSX]Data'!$E$2:$E$3=O24))=1,IF(SUMPRODUCT(--('[JV Data.XLSX]CODES'!$C$2:$C$300=LEFT(N24,3)))=1,TRUE,FALSE),TRUE)

I need to amend the underlined part of my formula somehow to be able to show TRUE if the left 3-7 characters matches a code on my list

This is a sample of my codes, only the top result shows TRUE on my current formula

 

[awoohaw]

Is there a way to sort your Org Code column alphabetically? Using the LOOKUP function may help with that.

Also, are the left most characters separated from the rest of the string by a consistent character (maybe a space)?

Can you label your cells with ColumnRow information please? And if you can post in a table or use the xl2bb add (link below, and preferred) it would be more helpful.

 

[chris0b]

hi Awoohaw!
My work laptop won't let me install XL2BB as I don't have admin privileges

I have put the row/column labels on the relevant cells, hopefully this helps!

Yes, the left most characters in Column N would be separated by a space, and everything in the Org Code list is 3-7 characters with no spaces

=IF(SUMPRODUCT(--('[JV Data.XLSX]Data'!$E$2:$E$3=O23))=1,IF(SUMPRODUCT(--('[JV Data.XLSX]CODES'!$C$2:$C$300=LEFT(N23,3)))=1,TRUE,FALSE),TRUE)

Column AG is where I have my formula,

This is the [JV Data.XLSX]Data'!$E$2:$E$3 part which works okay - this is on a different file which is normally closed

This is the [JV Data.XLSX]CODES'!$C$2:$C$300 part - this is also on a different file that is normally closed. Normally this is sorted alphabetically, I just tried to show some different lengths and that they have no pattern etc

 

[chris0b]

After some more searching I found a formula to count how many characters before the first space, so I can add this to my formula instead of just looking up the first 3 characters and it works on any length
=find(" ",a1)-1