#no Match Error using VLOOKUP

Bria Berger
Bria Berger ✭✭✭✭✭
edited 05/20/22 in Formulas and Functions

Hello - What I'm trying to do is return the name of the team member that owns the workstream onto a different sheet. I went with the vlookup formula and keep getting a no match error. Details and screenshots below. I want the formula to look for BCG on screenshot 1 and look for it on a different sheet. If it finds it, return the "Team Member" in the contact field of Screenshot 1. Any thoughts?

Formula I am using: =VLOOKUP([email protected], {Project Team - GES - 5% Workstream Range 4}, 9, false) (See screenshot)

Error I am getting: #NO MATCH

SCREENSHOT 1: This is the screen shot of the sheet I want the contact to be populated it.

This is the screenshot of the Range. Range is the entire sheet (see screenshot)


Best Answer

  • Mary Royston
    Answer ✓

    Hey @Bria Berger

    When using the Vlookup formula, the field you are searching has to be the left most column in the sheet you are searching. So if I understood what you are saying correctly, you want the formula to search for the Workstream in the sheet screenshot 2 and then feed back the name of the team member to sheet in screenshot 1?

    It could be giving you the #NoMatch error because it is unable to find that specific work stream in the left most column of your search range in screenshot 2.

    You could try the Index Match formula to see if that works better. Try something like = Index({Team Member Column in Screenshot 2}, Match([email protected], {work streamcolumn from sheet in Screenshot 2}, 0))

    See if that works for you, if not let me know!

Answers

  • Mary Royston
    Answer ✓

    Hey @Bria Berger

    When using the Vlookup formula, the field you are searching has to be the left most column in the sheet you are searching. So if I understood what you are saying correctly, you want the formula to search for the Workstream in the sheet screenshot 2 and then feed back the name of the team member to sheet in screenshot 1?

    It could be giving you the #NoMatch error because it is unable to find that specific work stream in the left most column of your search range in screenshot 2.

    You could try the Index Match formula to see if that works better. Try something like = Index({Team Member Column in Screenshot 2}, Match([email protected], {work streamcolumn from sheet in Screenshot 2}, 0))

    See if that works for you, if not let me know!

  • Bria Berger
    Bria Berger ✭✭✭✭✭

    Thank you, Mary! That worked like a charm. I didn't know about using the Index Match formula so really appreciate the tip. THANK YOU!!!