If statements: Non-numerical characters returning false when true

**JONNY981** · 06-24-2009, 07:42 AM

I have a formula in column A of the following sheet. The problem is that when the entry in cell H contains a letter as well as a number then the formula in column a returns 0.

Also if the value for false in the forumula is changed from 0 i.e to 1 or 2, then a value is returned in column A (this is shown in the red highlighted cells).

AssetRegexample.xls

Does anyone have any idea why this is?

**NBVC** · 06-24-2009, 07:45 AM

Are you actually trying to sum the numbers in column H or just return the asset number in column H when the the item in column I equals B and D equals K?

**JONNY981** · 06-24-2009, 08:57 AM

No not trying to sum the numbers, just want it to return the asset number from column H.

Thanks.

**NBVC** · 06-24-2009, 09:08 AM

Then are you looking for:

=IF(AND(B2=I2,D2=K2),H2,"")

copied down.

**JONNY981** · 06-24-2009, 09:32 AM

Thanks, but I need it to work for an array, i.e the formula needs to look through all of the data in columns I and K because the entries will not always necessarily be lined up correctly. The right hand columns are just for reference. Is it possible to do what I need to? Hopefully this makes sense!

Thanks.

**JONNY981** · 06-24-2009, 09:37 AM

Meant to upload this example file too, that first example didnt really show properly what I meant. This one shows how the data could be in columns H:K