# Match cell value and return another column value

1. ## Match cell value and return another column value

Hey guys,

I have roughly 30 sheets of data and I am looking to match values from one sheet to a range in another sheet and then return the value in a separate column of the same row.

Example:
• On Sheet1 I have a range of names (A2:A2000) and range of associated email addresses (AF2:AF2000)
• On Sheet5 I have a list of names (starting with A2)
• I want to add a formula in a column that takes the name in Sheet5!A2, finds the match in Sheet1!A2:A2000 and then returns the associated email address from Sheet1!AF2:AF2000

I have tried a few different formulas, however, I can't seem to make one that fits the needs:

Currently, Sheet5!A2 is matched to Sheet1!A756, so I need to return the value of Sheet1!AF756

2. ## Re: Match cell value and return another column value

Wouldn't a VLOOKUP work for this?

=VLOOKUP(Sheet5!A:A,Sheet1!A:AF,28,0)

3. ## Re: Match cell value and return another column value

I have tried a few very similar and just tried yours as well; however, I receive a #REF! error

***edit***
Actually, I am returning all "0" values

4. ## Re: Match cell value and return another column value

I think the reason you are getting the error is because the lists of names are not perfectly matched. It may be caused due to extra spaces.
Could you upload a sample, so we could check for ourselves?

5. ## Re: Match cell value and return another column value

I usually have a column of input and a second column of =TRIM(input) next to it. Then I do my lookups off of the trimmed column.

I work with part numbers, not names, but they still have tricky leading and trailing extra spaces - maybe that's what your issue is too.

6. ## Re: Match cell value and return another column value

There are no leading or trailing spaces as the additional sheets are populated from the original sheet (copy/paste) via a macro.

I cannot upload my actual document but I have provided an example sheet for testing (with notes).

7. ## Re: Match cell value and return another column value

thinkspac,

Entering a normal VLOOKUP into Sheet2!C1 worked fine:

=VLOOKUP(Sheet2!A:A,Sheet1!A:C,3,0)

Not sure what the problem is without seeing an example of your actual data set.

8. ## Re: Match cell value and return another column value

Let me see if i can't diagnose the deeper issue, as you are right, the standard VLOOKUP does work in this example.

I will go ahead and mark this as a resolved.

Thanks for the assistance!

There are currently 1 users browsing this thread. (0 members and 1 guests)