Vlookup woes

Steph_R

I'm trying to do a VLOOKUP and am getting the result #NOMATCH for all entries.

Essentially I want to look in the Instructor column in Sheet A, check Sheet B and find the Instructor name and return their ID number. The Instructor names are in the first column of Sheet B, like they're supposed to be, and the IDs are in the second column which I've referred to as 2 in the formula. If I use "false" I get the result #NOMATCH and if I use "true" it returns the same ID for all the names (and it isn't even someone in the Sheet A list).

my formula: =VLOOKUP("Instructor", {Instructor ID Range}, 2, false)

The Instructor ID Range is the first 2 columns of Sheet B.

Any idea what I'm doing wrong? I tried it in Excel and it worked perfectly. Thanks!

SYSPK

I've had this happen to me when the table I use for VLOOKUP is populated based on a formula. It likes the table to be text that you entered.

SYSPK

If that's not the case with you, could it be that something is misspelled, causing the names to not match up? Like is there a space after one of the names?

Andrée Starå

Hi Steph,

Can you describe your process in more detail and maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Mike Wilday

Have you tried recreating the cross-sheet reference? Sometimes those get wonky. 

Paul Newcome

VLOOKUP has always been a little wonky for me. I now use INDEX/MATCH statements instead. They are much more flexible and seem to be less picky about data types.

 

=VLOOKUP("Instructor", {Instructor ID Range}, 2, false)

 

as an INDEX/MATCH would look something like this...

 

=INDEX({Instructor ID Column}, MATCH("Instructor", {Instructor Name Column}, 0))

Andrée Starå

I agree with Paul.

When possible I always use INDEX/MATCH instead of VLOOKUP.

Steph_R

I figured it out! I was referencing the entire column and not the specific cell. Once I fixed that it worked like a charm. Thanks!

Andrée Starå

Excellent!

Happy to help!

Have a fantastic weekend!

Mike Wilday

I'm glad we were able to support you.