Link to home
Start Free TrialLog in
Avatar of Diane Lonergan
Diane LonerganFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Excel help with Match/Address functionality

I want to populate two cells in a table as rows are added, The  first column is each row has a drop down which references all the staff records on the pr tab. e.g.if Staff number 33 is selected from the dropdown on the absence table, the name will then populate from the PR table.
Employee-PR.xlsx
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image
Try this...

In B2
=IF([@StaffNo]="","",IFERROR(VLOOKUP([@StaffNo],MAsterPrTab[#All],2,0),""))

Open in new window


In C2
=IF([@StaffNo]="","",IFERROR(VLOOKUP([@StaffNo],MAsterPrTab[#All],3,0),""))

Open in new window


Employee-PR.xlsx
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image
Slight tweak to Neeraj's suggestion:

=IF([@StaffNo]="","",IFERROR(VLOOKUP([@StaffNo],MAsterPrTab[#All],MATCH(B$1,MAsterPrTab[#Headers],0),0),""))

Matching header will make more dynamic when copying.
Avatar of Diane Lonergan
Diane Lonergan
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

That's great . how would I amend it to allow me to pick up the surname too? I can see how to do it with a fixed column, by replacing the Match command with the value 3, but I like the more dynamic approach of your solution.
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Diane Lonergan
Diane Lonergan
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Thanks very much both of you and I have learned a lot from the solutions.