# strange result - Nested IF statement

#### CodyMonster

##### Board Regular
 A B C D E F G H Reporting Date Invoice ID invoice payment id invoice header id invoice terms date vendor number po/non-po flag supplier name 23-Apr-20 1900057573_5430_2020_0001064077 1900057573_5430_2020 0001064077 NON_PO GT TRANSPORT 2012 CO LTD 23-Apr-20 1900264963_6070_2020_0001058494 1900264963_6070_2020 0001058494 NON_PO NES GLOBAL TALENT RECRUITMENT SERVI 23-Apr-20 0027002369_6400_2020_0005508065 0027002369_6400_2020_0005508065 4/16/2020 0005508065 PO LUSH (HEAT TREATMENT) LTD 23-Apr-20 1900004280_L410_2020_ 1900004280_L410_2020 4/13/2020 NON_PO 0000315509

I'm getting the strangest result for the below nested if statement in "B". For some reason the statement doesn't pick up the supplier name if the vendor number "F" is blank.
I get D and the _ between the two cell references (D9 and H9) but not the number in column H.

=IF(AND(C9="",LEN(D9)<=20),D9&"_"&F9, IF(AND(C9="",F9=""),D9&"_"&H9,D9))

does anyone have an idea? I bet its something simple and I've been looking at this too long!

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### DanteAmor

##### Well-known Member
This part of your formula is true

IF(AND(C9="",LEN(D9)<=20),D9&"_"&F9

C9 = "" and LEN(D9) = 20, so : D9&"_"&F9
Then 1900004280_L410_2020 " & "_" & (blank)

#### CodyMonster

##### Board Regular
This part of your formula is true

IF(AND(C9="",LEN(D9)<=20),D9&"_"&F9

C9 = "" and LEN(D9) = 20, so : D9&"_"&F9
Then 1900004280_L410_2020 " & "_" & (blank)

Thanks Dante,
Found something that worked.

=IF(AND(C8="",LEN(D8)<=20,F8<>""),D8&"_"&F8, IF(AND(C8="",LEN(D8)=20),D8&"_"&H8,D8))

Replies
0
Views
102
Replies
1
Views
176
Replies
3
Views
473
Replies
5
Views
1K

1,129,539
Messages
5,636,903
Members
416,949
Latest member
propertyscout

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back