Forum Discussion
Help with Formula
=IF(OR(RIGHT(B2,4)="1101",C2="Salary"),INDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=A2)*($C$2:$C$9="Salary"),0)),B2)
You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.
Thank you so much for your help with this.
I have used this formula and it looked like it had worked. I copied the formula down to include all the rows (2170) and it seemed to stop working further down the rows. I copied out a sample for one person and the results of the formula:
The column R shows the results when the data was included in the full listing. The Column U is when I tried applying the formula to the data independently in this cut of it.
Here's the formula for the first row above used in the main data:
=IF(OR(RIGHT(C106,4)="1101",G106="Salary"),INDEX($C$1:$C$2170,MATCH(1,($B$1:$B$2170=B106)*($G$1:$G$2170="Salary"),0)),C106)
Here's the formula used in Column U above:
=IF(OR(RIGHT(C1,4)="1101",G1="Salary"),INDEX($C$1:$C$9,MATCH(1,($B$1:$B$9=B1)*($G$1:$G$9="Salary"),0)),C1)
I'm not sure why there's a difference.
Peter_Browning The MATCH function will return the first match found where the criteria evaluates to TRUE. Since the results you're getting for a particular employee are not what you expected, it means the criteria is not unique to that employee. The most likely explanation, based on the data you've shared, is the same reference number is being used for more than one employee. Try using additional criteria to return the first match where the reference number, first name and last name are equal to the values in the current row. For example, the formula you used in row 106 can be re-written as follows:
=IF(RIGHT(C106, 4)="1101", INDEX($C$1:$C$2170, MATCH(1, ($B$1:$B$2170=B106)*($D$1:$D$2170=D106)*($E$1:$E$2170=E106)*($G$1:$G$2170="Salary"), 0)), C106)Or, with the XLOOKUP function (requires Excel 2021 or later):
=IF(RIGHT(C106, 4)="1101", XLOOKUP(1, ($B$1:$B$2170=B106)*($D$1:$D$2170=D106)*($E$1:$E$2170=E106)*($G$1:$G$2170="Salary"), $C$1:$C$2170, C106), C106)You are welcome. The formula in column R refers to 2170 rows and the formula in column U refers to 9 rows. The screenshot shows 9 rows therefore it's impossible to tell why there are different results. I could only guess why the results are different. Can you attach your file without sensitive data so we can have a look at this?
I am sorry for the delay in replying. Things have been a bit crazy here just at the moment.
I am attaching the data anonymised as requested. I have also found out that the original logic I gave isn't quite correct. Originally I said I wanted to show the code for each employee ending in "1101" to match that of the record for that employee where the Type is "Salary". I now learn that an employee can have either a "Salary" or a "Basic" so either of these we need to look for. It is also possible that an employee could have two "basic" Types, but I am informed in these cases both Basic types will have the same Code.
I hope that makes sense. Seeing the actual data should help.
