LEFT function not resolving correctly

**inky** · 12-01-2007, 05:10 AM

in the following function the LEFT function is not resolving correctly. when i break the individual components out they resolve correctly, yet when i bring them together the LEFT returns a FALSE when it is in fact TRUE. any thoughts?

=IF(ISNUMBER($B33),MATCH($B33,AcctNum,0),IF((LEFT($C33="P")),MATCH($B33,AcctLine,0),MATCH($B33,ReportLine,0)))

what the formula is trying to do is:
1. look at B33 and determine if it is an account number or an account name
2. if it is an account number, it is to look up a range of account numbers.
3. if it is an account name (ie not an account number), it is then to look into C33 to determine if the account name comes from a "Profit" or "Report"; it does this by determining if C33 begins with a "P" or not.
4. depending on whether it's a profit or a report item it then looks up the appropriate range.

i will include an IF to return a "Valid", "Invalid" result accordingly, yet the LEFT and MATCH is failing to return the reference. at the moment B33 is text and C33 is "Profit".

**davesexcel** · 12-01-2007, 06:10 AM

Originally Posted by inky

in the following function the LEFT function is not resolving correctly. when i break the individual components out they resolve correctly, yet when i bring them together the LEFT returns a FALSE when it is in fact TRUE. any thoughts?

=IF(ISNUMBER($B33),MATCH($B33,AcctNum,0),IF((LEFT($C33="P")),MATCH($B33,AcctLine,0),MATCH($B33,ReportLine,0)))

what the formula is trying to do is:
1. look at B33 and determine if it is an account number or an account name
2. if it is an account number, it is to look up a range of account numbers.
3. if it is an account name (ie not an account number), it is then to look into C33 to determine if the account name comes from a "Profit" or "Report"; it does this by determining if C33 begins with a "P" or not.
4. depending on whether it's a profit or a report item it then looks up the appropriate range.

i will include an IF to return a "Valid", "Invalid" result accordingly, yet the LEFT and MATCH is failing to return the reference. at the moment B33 is text and C33 is "Profit".

Hi Inky,
what do you want to happen, when there is a match?

**inky** · 12-01-2007, 07:17 AM

if it's true, ie the line item or account is in the list, i want the cell that contains the formula (let's say D33) to display "Valid". if it's not on the list, i want the cell to display "Invalid".

i haven't added that IF, but it will encase the current formula.

**davesexcel** · 12-01-2007, 08:17 AM

Here's a vlookup example that you could try,
select an item from the drop down menu, to see how the formula changes when you select an item that is not on the list

**daddylonglegs** · 12-01-2007, 08:30 AM

You have a parenthesis in the wrong place. You have

=LEFT($C33="P")

this needs to be

=LEFT($C33)="P"

**inky** · 12-01-2007, 06:09 PM

thanks ddl... i guess staring at rows and columns for too long can get to you!

**inky** · 12-02-2007, 02:28 AM

hmmm... so ive now encased that formula in an if, yet when the formula doesn't find the account or line it displays "N/A" rather than "Invalid". any suggestions?

=IF(IF(ISNUMBER($B33),MATCH($B33,AcctNum,0),IF((LEFT( $C33="P")),MATCH($B33,AcctLine,0),MATCH($B33,Repor tLine,0))),"Valid","Invalid")

these "simple" formulas are scarring and confusing me :o(

**inky** · 12-02-2007, 02:55 AM

got it - used an ISERROR! yet i'm now curious as to whether there's another way?

**BigBas** · 12-02-2007, 03:02 AM

Your formula is kinda wacky. If you post a .zip sample of your file, it will be easier to fix it for you.

I'll give it a shot:

Please Login or Register  to view this content.

1. You do not need two IFs to start it off. =IF(ISNUMBER($B33) should suffice
2. LEFT($C33="P") should be LEFT($C33)="P"
3. You have too many conditional "answers". Here is the progression of the formula:
a. If B33 is a number, then match B33 in AcctNum. If not, then IF the first letter of C33 is a P, match B33 with Acctline. If C33 is not a P, then match B33 with reportLine. I'm not sure how invalid and valid play into the equation.

Again, post a .zip sample, and I'm sure we can figure it out.