Vlookup cross-sheet reference

nlenehan

Hi,

My vlookup gets a "#NOMATCH' for one value, but works for all the other values. It works for everything except 'Manufacturing' - see screenshot.

=VLOOKUP(Department@row, {Executive Email For Approval}, 4)

Any ideas?

Thanks, Nichola

Genevieve P.

Hi @nlenehan

If it's working for the others except one value, it may be that there's a slight spelling difference between how Manufacturing is spelled in your column Department in the sheet with the formula, and how it's spelled in the column in the other sheet. Is that possible?

It would be helpful to see a screen capture of the sheet that contains the formula, if the spelling is the exact same. It would also be helpful to know how the values in the Department column are being input (is there another formula, or are they manually typed in, etc).

Thanks!

Genevieve

nlenehan

I know, sounds like user error, right? I cut and pasted 'manufacturing' from one sheet to the other to see if that was the issue. It didn't make any difference.

Genevieve P.

Hmm, very strange!!

Could you try using an INDEX(MATCH instead? It works in a similar way, but you just select the columns you need for the formula instead of an entire range.

An INDEX(MATCH works like this:

=INDEX({Column with value to return}, MATCH(Department@row, {Department column in the other sheet}), 1)

Does that give you a correct result?