How to have a cell populate a value from one list if a value from another list is entered into another cell.

Question

In a spreadsheet, I am tracking reimbursement for travel. I have a list of cities (list A) we travel to, and next to it the reimbursement amount (list B). How can I get the appropriate amount populate in the reimbursement column (cell B) when a specific city is entered in the location column (cell A)?

Answer 1

Hi @Brandon Devlin,

Thank you for posting your question in the Microsoft Q&A forum.

To populate the correct reimbursement amount in cell B based on the city entered in cell A, you should use the XLOOKUP function. You can try following these steps to see if it meets your need:

• In my test worksheet, I have a sample list like this

• Starting from cell D1, I will enter the city name, and I want cell E1 returns the according reimbursement data of that city. To achieve that, I will use this formula: =XLOOKUP(D1,$A$2:$A$4,$B$2:$B$4,"No data",0)
• This formula will return the exact reimbursement value according with the city name in cell D1. If the city isn't listed, it will return "No data"

I hope the information provided proves useful. Please proceed with the outlined steps and let me know whether they resolve the issue. If not, I’ll be glad to continue working with you to find a solution.

Thank you for your patience and understanding throughout this process. Should you have any questions or need further assistance, feel free to reach out at any time.

I look forward to hearing from you.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".    

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.