Forum Discussion
Match and If ?
I am trying to put the match and if functions into a formula (if they are the correct ones to use)
I have two sheets, "Front" and Expenses" In Column E on the Front sheet i want to look in Column A on the expenses sheet, find the match, i.e "expenses sheet a5" and then return in Front sheet the expenses amount in "expenses column" B for the match.
Then to use this in the following cells in column E to put in the correct expenses, and i no expenses claimed a "0".
Front Sheet
| A | B | C | D | E | F |
| Employee First Name | Employee Surname | Payroll ID | Pay | Expenses | Total |
| Joe | Smith | SMI100 | £ 100.00 | £ 100.00 | |
| George | Franks | FRA100 | £ 110.00 | £ 110.00 | |
| Jim | Bowen | BOW300 | £ 90.00 | £ 90.00 | |
| Nigel | Fox | FOX300 | £ 80.00 | £ 80.00 |
Expenses Sheet
| Test Expenses | |
| Payroll ID | Expenses |
| BOW300 | £ 60.00 |
| FRA100 | £ 70.00 |
| SMI100 | £ 80.00 |
| FOX300 | £ 90.00 |
Any ideas would be welcome
1 Reply
In E2 on the Front sheet:
=IFERROR(VLOOKUP(C2,Expenses!$A$3:$B$6,2,FALSE),"")
If you have Microsoft 365 or Office 2021), you can also use
=XLOOKUP(C2,Expenses!$A$3:A$6,Expenses!$B$3:$B$6,"")
Fill down.
