Forum Discussion
Comparing data to return a value
Hi,
I have 5 columns of data. If A and B match E and F, I want the code in G to appear in C. Please can someone help with the formula?
Many thanks,
Emma
| A | B | C | D | E | F | G |
| Description | Dept | Expense Code | Description | Dept | Expense Code | |
| Travel | HR | Travel | HR | 7101 | ||
| Travel | Finance | Travel | Finance | 7103 | ||
| Travel | Transport | Travel | Operations | 7104 | ||
| Subsistence | Transport | Travel | Transport | 7105 | ||
| Other | Operations | Subsistence | HR | 7151 | ||
| Subsistence | Operations | Subsistence | Finance | 7153 | ||
| Travel | Finance | Subsistence | Operations | 7154 | ||
| Subsistence | Transport | 7158 | ||||
| Other | HR | 7108 | ||||
| Other | Finance | 7118 | ||||
| Other | Operations | 7128 | ||||
| Other | Transport | 7138 |
Hi Emma,
You can depend on this formula:
=IF(AND(A2=E2,B2=F2),G2,"")
Please find the attached file.
Thanks for this. Does it not assume though that the correct answer is on the same row?
Haytham Amairah wrote:
Hi Emma,
You can depend on this formula:
=IF(AND(A2=E2,B2=F2),G2,"")
Please find the attached file.
Sorry about that!
Please use this formula instead:
=INDEX($G$2:$G$13,INDEX(MATCH(A2&B2,$E$2:$E$13&$F$2:$F$13,0),))
Please find the attached file.
