Need help as vlook up not working

RobertZapata

Hi smartsheet commuinty,

have an issue on vlook up formula as telling me "no match" but i did key in the formula as the data cell 12th column is not being taken.

Andrée Starå

Hi @RobertZapata

I hope you're well and safe!

The NO MATCH indicates that it couldn't find a matching value for the lookup.

Double-check that the IDs exist in the same format in both sheets.

#NO MATCH

Causes

• The VLOOKUP function hasn't found a result.
• There is not a number within the range that is greater than or equal to the search_value in the VLOOKUP function.

Resolution

Adjust the search_value or lookup_table of the VLOOKUP function such that it finds a result. (Learn more about VLOOKUP.)

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

RobertZapata

Hi @Andrée Starå,

I have checked and i have used correct column number with is 12.

it is available and i really couldn't understand why it does not take the formula

Nick Korna

Hi @RobertZapata,

What range is on your cross sheet reference?

If the screenshot in you post previous to this one is the range, the VLOOKUP isn't finding the value because the Supply Chain ID is the 3rd column, not the leftmost one or there would need to be 3 more columns to the right of your screenshot that we can't see for it to be reading the 12th column value.

If you amend the range and/or reduce the column number down to 10 then your VLOOKUP should be able to find the Supply Chain Status based on the Supply Chain ID.

Hope this helps, but if you've any more problems/questions with this then just post them.

RobertZapata

Hi @Nick Korna ,

Actually i am doing a VLOOKUP from this sheet as i wanted to get supply chain status from the other sheet.

As you could see below it is on column 12 hence i used the formula as 12 but it is telling no match.

Appreciate to help me oout as this is a problem for me as i can't seem to diagnose the formula properly.

Nick Korna

If your data sheet is like this:

Then to do the lookup on your other sheet, you should use a the following formula:

=VLOOKUP([Supply Chain ID]@row, {Approved Supplier List}, 10, false)

Where your cross sheet reference is like this (with the right most 10 columns highlighted):

Which will give a result like this:

This is why your VLOOKUP isn't working - while the column you want is the 12th column in the sheet, this isn't what is matching with the Supply Chain ID - if you're using the range above, it is the 10th. For a VLOOKUP, the value you are looking up must be the leftmost one in the range.

If you wanted to use INDEX/MATCH instead:

=INDEX({Supply Chain Status}, MATCH([Supply Chain ID]@row, {Supply Chain ID}, 0))

Where the cross sheet references refer to the relevant columns on your data sheet, this would enable them to be in any order (e.g. if you had your Supply Chain Status as the leftmost column) which is a distinct advantage over VLOOKUP where the lookup column has to be the leftmost one.

Hope this clears things up for you, let me know if not!

RobertZapata

Hi @Nick Korna ,

Thanks as it is working but just wondering why the first 2 column's doesn't need to be selected as part of search table?

Thanks again.

Nick Korna

When using a VLOOKUP the first column in your range is the one to compare with the search value. In this case, this excludes the first 2 columns as otherwise you'll get #NO MATCH errors (as you found). INDEX/MATCH is generally better for doing the checks.