Lookup and Data Formats

[statenja]

I'm comparing data from 2 different sources and looking for matches. I think the cell formatting must be different but I can't figure out how.

My equation is:
=LOOKUP(2,1/((TRIM(I2)=$C$2:$C$6)*(TRIM(J2)=$D$2:$D$6)*(K2=$E$2:$E$6)*(TRIM(L2)=$F$2:$F$6)*(M2=$G$2:$G$6)),$B$2:$B$6)

Notice the "TRIM" function is used in column I,J,L because the data from that source comes in with an extra space.
The equation seems right, as it worked when I typed in the data in the 3rd row.

Can someone give me any hints why the value in Cell O2 is not displaying properly?

See attached

[rylo]

Hi

Try
=LOOKUP(2,1/((SUBSTITUTE(I2,CHAR(160),"")=$C$2:$C$6)*(SUBSTITUTE(J2,CHAR(160),"")*1=$D$2:$D$6)*(K2=$E$2:$E$6)*(SUBSTITUTE(L2,CHAR(160),"")*1=$F$2:$F$6)*(M2=$G$2:$G$6)),$B$2:$B$6)

The trailing character has a code value of 160, not 32 and isn't a normal space. From the help file

The TRIM function was designed to trim the 7-bit ASCII space character (value 32) from text. In the Unicode character set, there is an additional space character called the nonbreaking space character that has a decimal value of 160

So I've used SUBSTITUTE to remove this character. However, this returns a text value, and this has to be converted to numeric. I've done that by using *1.

HTH

rylo

[statenja]

That worked!!! Thank you so much. This is going to be a huge timesaver!