Jun 17 2020 08:49 PM
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.
A | B | C | D | E | |
1 | IRN Number | IRN Date | Vendor Code | Vendor Name | Invoice No |
2 | IRN/202105028 | 30-May-2020 | V0240 | M/s SRI KARTIKEYA POLYMERS | 49 |
3 | IRN/202105029 | 30-May-2020 | V0369 | M/s MARUTI ELECTRICALS | ME/3 |
4 | IRN/202105029 | 30-May-2020 | V0369 | M/s MARUTI ELECTRICALS | ME/3 |
5 | IRN/202105029 | 30-May-2020 | V0369 | M/s MARUTI ELECTRICALS | ME/3 |
6 | IRN/202105029 | 30-May-2020 | V0369 | M/s MARUTI ELECTRICALS | ME/3 |
7 | IRN/202105030 | 30-May-2020 | V0369 | M/s MARUTI ELECTRICALS | ME/2 |
8 | IRN/202105030 | 30-May-2020 | V0369 | M/s MARUTI ELECTRICALS | ME/2 |
9 | IRN/202105030 | 30-May-2020 | V0369 | M/s MARUTI ELECTRICALS | ME/2 |
10 | IRN/202105030 | 30-May-2020 | V0369 | M/s MARUTI ELECTRICALS | ME/2 |
11 | IRN/202105030 | 30-May-2020 | V0369 | M/s MARUTI ELECTRICALS | ME/2 |
12 | IRN/202105030 | 30-May-2020 | V0369 | M/s MARUTI ELECTRICALS | ME/2 |
13 | IRN/202105030 | 30-May-2020 | V0369 | M/s MARUTI ELECTRICALS | ME/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
Jun 17 2020 10:48 PM - edited Jun 17 2020 10:49 PM
Solution
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.
Jun 17 2020 11:10 PM
IRN Number | IRN Date | Vendor Code | Vendor Name | Invoice No | V0369 | ME/2 | |
IRN/202105028 | 30-May-20 | V0240 | M/s SRI KARTIKEYA POLYMERS | 49 | |||
IRN/202105029 | 30-May-20 | V0369 | M/s MARUTI ELECTRICALS | ME/3 | |||
IRN/202105029 | 30-May-20 | V0369 | M/s MARUTI ELECTRICALS | ME/3 | |||
IRN/202105029 | 30-May-20 | V0369 | M/s MARUTI ELECTRICALS | ME/3 | |||
IRN/202105029 | 30-May-20 | V0369 | M/s MARUTI ELECTRICALS | ME/3 | |||
IRN/202105030 | 30-May-20 | V0369 | M/s MARUTI ELECTRICALS | ME/2 | |||
IRN/202105030 | 30-May-20 | V0369 | M/s MARUTI ELECTRICALS | ME/2 | |||
IRN/202105030 | 30-May-20 | V0369 | M/s MARUTI ELECTRICALS | ME/2 | |||
IRN/202105030 | 30-May-20 | V0369 | M/s MARUTI ELECTRICALS | ME/2 | |||
IRN/202105030 | 30-May-20 | V0369 | M/s MARUTI ELECTRICALS | ME/2 | |||
IRN/202105030 | 30-May-20 | V0369 | M/s MARUTI ELECTRICALS | ME/2 | |||
IRN/202105030 | 30-May-20 | V0369 | M/s MARUTI ELECTRICALS | ME/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,"") |
Jun 18 2020 04:37 AM
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
Jun 18 2020 09:24 AM
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))
Jun 22 2020 03:24 AM
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......
Jun 17 2020 10:48 PM - edited Jun 17 2020 10:49 PM
Solution
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.