Forum Discussion
Help
Hello,
if you want the agent number to appear next to the customer name, in column B on the sheet on the right hand side of your screenshot, then you need an Index/Match combination. You cannot use VLookup in this scenario, because for VLookup the lookup column must be on the left and in your data the lookup column (the customer names) is to the right and the agent is on the left.
Let's assume that the sheet on the left hand side of your screenshot is called Sheet1 and you are entering the formula into cell B1 on the other sheet. It goes like this:
=index(Sheet1!A:A,match(Sheet2!A1,Sheet1!B:B,0))
The Match() function looks up the name from cell A1 and finds it in Sheet1, column B. Match returns a number. Index then uses that number as the row number for column A in Sheet1 and the formula returns the agent.
Does that make sense?
Hi Andreas,
Thank you for your response.
So I need column A in spread sheet 2, matched to column B in spread sheet 1, If they are exact matched to equal the name in column A on spread sheet 1, So the agent name would then appear next to the customers name, Does that make sense?
Example attached
Hello,
if you want the agent number to appear next to the customer name, in column B on the sheet on the right hand side of your screenshot, then you need an Index/Match combination. You cannot use VLookup in this scenario, because for VLookup the lookup column must be on the left and in your data the lookup column (the customer names) is to the right and the agent is on the left.
Let's assume that the sheet on the left hand side of your screenshot is called Sheet1 and you are entering the formula into cell B1 on the other sheet. It goes like this:
=index(Sheet1!A:A,match(Sheet2!A1,Sheet1!B:B,0))
The Match() function looks up the name from cell A1 and finds it in Sheet1, column B. Match returns a number. Index then uses that number as the row number for column A in Sheet1 and the formula returns the agent.
Does that make sense?
Perfect thank you so much.
That worked.
