Formula help please

Tracey Tume

Hi

I am trying the formula below and it is returning a #NO MATCH (see image below for the Factory QC Signoff Ass n Fin reference sheet)

=VLOOKUP([Clean Job Number]@row, {Factory QC Signoff Ass n Fin}, 5, false)

Perhaps the issue is that the "Clean Job Number" column has a formula to extra just the 5 digits of our job number?

=LEFT([Job Number]@row, 5)

Any help will be gratefully received

Genevieve P.

Hi @Tracey Tume

Yes, it's possible that in one sheet your Job Number is seen as numerical values but in your current sheet with the VLOOKUP formula, the "clean" number is read as a text string, so it's not finding a match.

Try wrapping a VALUE function around your current "clean" formula, like so:

=VALUE(LEFT([Job Number]@row, 5))

This should change it to be read as a number. Let me know if this resolves the issue!

As a side-note, I would personally recommend using INDEX(MATCH instead of using VLOOKUP. This is because an INDEX(MATCH has two separate ranges for your two columns, and doesn't require you to select a range across unnecessary data (e.g. "Description", "Name" columns).

Because the two columns are selected individually, this means you could change the organization of your source sheet, moving columns around, and it won't break any formulas. It also pairs down the number of cells being referenced which will reduce complexity and help the sheet run faster.

Here's the structure of an INDEX(MATCH:

=INDEX({Column to return}, MATCH("Matching Value", {Column with value to match}, 0))

So in your case:

=INDEX({Finishers Name Column}, MATCH([Clean Job Number]@row, {Job Number Column}, 0))

See: Formula combinations for cross sheet references

Cheers,

Genevieve

Genevieve P.

Hi @Tracey Tume

Yes, it's possible that in one sheet your Job Number is seen as numerical values but in your current sheet with the VLOOKUP formula, the "clean" number is read as a text string, so it's not finding a match.

Try wrapping a VALUE function around your current "clean" formula, like so:

=VALUE(LEFT([Job Number]@row, 5))

This should change it to be read as a number. Let me know if this resolves the issue!

As a side-note, I would personally recommend using INDEX(MATCH instead of using VLOOKUP. This is because an INDEX(MATCH has two separate ranges for your two columns, and doesn't require you to select a range across unnecessary data (e.g. "Description", "Name" columns).

Because the two columns are selected individually, this means you could change the organization of your source sheet, moving columns around, and it won't break any formulas. It also pairs down the number of cells being referenced which will reduce complexity and help the sheet run faster.

Here's the structure of an INDEX(MATCH:

=INDEX({Column to return}, MATCH("Matching Value", {Column with value to match}, 0))

So in your case:

=INDEX({Finishers Name Column}, MATCH([Clean Job Number]@row, {Job Number Column}, 0))

See: Formula combinations for cross sheet references

Cheers,

Genevieve

Tracey Tume

Hi Genevieve

I must be a nerd and I was so excited when this worked! ha ha

I love learning new formulas so both of those suggestions are ones I will use again.

Many thanks.

Tracey

Genevieve P.

Hi @Tracey Tume

We're all nerds, here! 🤓 Glad it worked for you.

Genevieve