SOLVED

IF Formula with multiple criteria with

Copper Contributor

Hi, i was trying to if function for finding the same criteria in the above rows with multiple criteria based with if condition it's possible please help us.

 

 

 ABCDE
1IRN NumberIRN DateVendor CodeVendor NameInvoice No
2IRN/20210502830-May-2020V0240M/s SRI KARTIKEYA POLYMERS49
3IRN/20210502930-May-2020V0369M/s MARUTI ELECTRICALSME/3
4IRN/20210502930-May-2020V0369M/s MARUTI ELECTRICALSME/3
5IRN/20210502930-May-2020V0369M/s MARUTI ELECTRICALSME/3
6IRN/20210502930-May-2020V0369M/s MARUTI ELECTRICALSME/3
7IRN/20210503030-May-2020V0369M/s MARUTI ELECTRICALSME/2
8IRN/20210503030-May-2020V0369M/s MARUTI ELECTRICALSME/2
9IRN/20210503030-May-2020V0369M/s MARUTI ELECTRICALSME/2
10IRN/20210503030-May-2020V0369M/s MARUTI ELECTRICALSME/2
11IRN/20210503030-May-2020V0369M/s MARUTI ELECTRICALSME/2
12IRN/20210503030-May-2020V0369M/s MARUTI ELECTRICALSME/2
13IRN/20210503030-May-2020V0369M/s MARUTI ELECTRICALSME/2

 

i was trying formula in A14 trying with if formula, actually i want the IRN Number "IRN/202105030 if C14 = V0369 & E14= ME/2, if nothing in C14 and E14, it will come 0, but i dont get the formula please help us

5 Replies
best response confirmed by sreekittu (Copper Contributor)
Solution

@sreekittu 

 

If you want a formula in A14 which populates A14 with matching IRN Number for the Vendor Code entered in C14 and Invoice No entered in E14, please try something like this...

 

 

=IF(OR(C14="",E14=""),0,IFERROR(INDEX($A$2:$A$13,MATCH(C14&E14,INDEX($C$2:$C$13&$E$2:$E$13,),0)),0))

 

 

As per the above formula if you enter V0369 in C14 ad ME/2 in E14, the formula would return IRN/202105030 in A14.

 

@sreekittu 

IRN NumberIRN DateVendor CodeVendor NameInvoice NoV0369ME/2
IRN/20210502830-May-20V0240M/s SRI KARTIKEYA POLYMERS49  
IRN/20210502930-May-20V0369M/s MARUTI ELECTRICALSME/3  
IRN/20210502930-May-20V0369M/s MARUTI ELECTRICALSME/3  
IRN/20210502930-May-20V0369M/s MARUTI ELECTRICALSME/3  
IRN/20210502930-May-20V0369M/s MARUTI ELECTRICALSME/3  
IRN/20210503030-May-20V0369M/s MARUTI ELECTRICALSME/2  
IRN/20210503030-May-20V0369M/s MARUTI ELECTRICALSME/2  
IRN/20210503030-May-20V0369M/s MARUTI ELECTRICALSME/2  
IRN/20210503030-May-20V0369M/s MARUTI ELECTRICALSME/2  
IRN/20210503030-May-20V0369M/s MARUTI ELECTRICALSME/2  
IRN/20210503030-May-20V0369M/s MARUTI ELECTRICALSME/2  
IRN/20210503030-May-20V0369M/s MARUTI ELECTRICALSME/2  
RESULTS BELOW  FORMULA USED ON EACH ROW   
 
 

 

 
 =IF(AND(C2=$F$1,E2=$G$1),A2,"")   
   =IF(AND(C3=$F$1,E3=$G$1),A3,"")   
   =IF(AND(C4=$F$1,E4=$G$1),A4,"")   
   =IF(AND(C5=$F$1,E5=$G$1),A5,"")   
   =IF(AND(C6=$F$1,E6=$G$1),A6,"")   
IRN/202105030  =IF(AND(C7=$F$1,E7=$G$1),A7,"")   
IRN/202105030  =IF(AND(C8=$F$1,E8=$G$1),A8,"")   
IRN/202105030  =IF(AND(C9=$F$1,E9=$G$1),A9,"")   
IRN/202105030  =IF(AND(C10=$F$1,E10=$G$1),A10,"")   
IRN/202105030  =IF(AND(C11=$F$1,E11=$G$1),A11,"")   
IRN/202105030  =IF(AND(C12=$F$1,E12=$G$1),A12,"")   
IRN/202105030  =IF(AND(C13=$F$1,E13=$G$1),A13,"")   
   =IF(AND(C14=$F$1,E14=$G$1),A14,"")   
   =IF(AND(C15=$F$1,E15=$G$1),A15,"")   
   =IF(AND(C16=$F$1,E16=$G$1),A16,"")   

Thank you@Subodh_Tiwari_sktneer 

 

It's working but i need one more formula add with existing your formula

 

if return same IRN number will come no Problem, but if answer is "0" means i need next IRN number in the last IRN number (exmple IRN/202105030 is Last IRN number, but i need IRN/202105031 in next "0" Place, please help me@Subodh_Tiwari_sktneer 

@sreekittu 

Okay, give this a try and see if this is what you are trying to achieve.

 

=IF(OR(C14="",E14=""),0,IFERROR(INDEX(A$2:A13,MATCH(C14&E14,INDEX(C$2:C13&E$2:E13,),0)),"IRN/"&MAX(INDEX(SUBSTITUTE(A$2:A13,"IRN/","")*1,))+1))

 

HI@Subodh_Tiwari_sktneer ,

 

Actually its working, but IRN/202105002 meaning is "Item Receipt Note" (IRN)/ 2021 means Fy Year is 2021, 05 Means Month of IRN, 002 means Serial Number in the IRN month, so i need if IRN/20210503 will come the answer it will show the IRN/202105004, if IRN revived in 1st Date in June answer will come IRN/202106001, so please try the formula like this......  

1 best response

Accepted Solutions
best response confirmed by sreekittu (Copper Contributor)
Solution

@sreekittu 

 

If you want a formula in A14 which populates A14 with matching IRN Number for the Vendor Code entered in C14 and Invoice No entered in E14, please try something like this...

 

 

=IF(OR(C14="",E14=""),0,IFERROR(INDEX($A$2:$A$13,MATCH(C14&E14,INDEX($C$2:$C$13&$E$2:$E$13,),0)),0))

 

 

As per the above formula if you enter V0369 in C14 ad ME/2 in E14, the formula would return IRN/202105030 in A14.

 

View solution in original post