Formula to change cell based on info from another sheet?

vdemattei

Hi @Paul Newcome, you offered to help find a solution to a problem that was discussed in another thread if I started a new thread and tagged you... so here goes! (Thank you, by the way).

I have a sheet that tracks progress through a workflow. One of the items I'd like to track is if a ID number appears on another sheet. If it does, I'd like to ultimately check a box on the workflow sheet. I've attempted using index match this way (I've never successfully used this type of formula, though I've watched countless videos about it and I just can't seem to figure out how to make this work for my use):

=index(match([the customer ID number on the workflow sheet],[the column that has customer ID numbers on the source sheet], ... then what?

Maybe index match isn't the best formula to use?

All I want is "look for this number on the other sheet in this column, can you find it there? yes or no". It seems fairly simple, but I'm stumped.

Thanks for your help!

Paul Newcome

Ok. Try putting that same helper column also in the source sheet and reference that in your formula as well.

vdemattei

I'm still working on this in the meantime... I really think index match should work, the more I read about this. so maybe my syntax isn't quite right. at this point, i'm just trying to get ANY information to return IF the ID number is found.

=INDEX({homeowner name}:{homeowner name},MATCH([Customer ID]@row,{Customer ID}:{Customer ID},0))

Where "{homeowner name}" and "{Customer ID}" are columns selected from another source sheet, once on either side of the colon. I've tried just selecting the column without the colons and that doesn't work either. I'm using "homeowner name" as a piece of information to return if the ID number is found - it doesn't matter if it's the name or anything else on that sheet.

I have at least 3 different instances where it would be super helpful if I could get this to work. Any specific help would be much appreciated. There is a lot of information out there regrading index match and similar formulas, but I just can't seem to get it to work for my specific need.

JamesB

@vdemattei You could use a Vlookup to get the results you need.

=Vlookup([Customer ID]@row,{Lookup Sheet Range 1},99,false)

Where I have the 99 is the column number to return, just replace it with the correct number. Your Customer ID column that you are looking up from is column 1 and the column you want to return is the count over from customer ID. Be sure when you create your lookup range that you include the Homeowner name in the range selection.

vdemattei

https://community.smartsheet.com/discussion/comment/389057#Comment_389057

Thanks for the response. I'm trying what you suggested. If the homeowner column is directly to the right of the customer ID column, would I use "2" for the column number to return? Customer ID would be column 1 in the table and homeowner name would be column 2 in the table, right?

I'm getting "no match" unfortunately. I also have concerns about using vlookup as i understand it might get messed up if the columns move. Is that right? If I can get it to work, though, I'll take it!

Paul Newcome

When using cross sheet references, you only have to enter them once.

=INDEX({homeowner name},MATCH([Customer ID]@row,{Customer ID},0))

Paul Newcome

And you are also correct hat VLOOKUP is extremely rigid. The lookup value MUST be on the right side of your range. If the columns on the source sheet get moved it can break your formula. And it references A LOT more cells since you have to reference not just the lookup column and the pull column but also everything in between.

With INDEX/MATCH you are only referencing the two columns you need, and since you are referencing them individually, you don't have to worry about where they are in relation to each other on the source sheet.

vdemattei

https://community.smartsheet.com/discussion/comment/389180#Comment_389180

Thank you for your response! I tried exactly this and it's giving me a "NO MATCH" result. I'm stumped... Could it be because the Customer ID number is automatically generated? The column type is "# Auto Number"... if that's the case, is there a work around? I think I'd make a duplicate column and copy the information from the Auto number column, but I'd still be using a formula... so that probably won't work either?

Paul Newcome

I reference auto-number columns in formulas all the time.

It looks like you are comparing Customer ID and Project ID for the match? Can you provide a screenshot of the auto-number's column properties, and how exactly is the Project ID being populated?

vdemattei

Ok, that's good to know.

From my images above: "Customer ID" is the original auto-number column.

Here are the column properties:

"Project ID" in the second photo above is the same as Customer ID (Maybe I should rename it to be consistent), but that is manually entered from a form that then populates the second sheet/image above.

And yes, I'm trying to match Customer ID to Project ID... but they are exactly the same thing.

Paul Newcome

Ok. So in this instance it isn't necessarily that the column is an auto-number column. The issue is how you are having it populated. Because of the prefix, the auto-number column data is being stared as text values, but when you manually enter the project ID it is being stored as a number value. Number and text values don't match even if they look the same.

Try this:

=INDEX({homeowner name},MATCH([Project ID]@row + "",{Customer ID},0))

vdemattei

Ok, that's really good to know...

Unfortunately, I'm still getting "No match".

Here's my formula for reference:

Btw, I renamed the Project ID column to Customer ID, so it's the same in both sheets.

Paul Newcome

Ok. Lets try a helper column in the target sheet and use this formula:

=[Customer ID]@row + ""

Then reference this column in your MATCH function.

vdemattei

Gosh, I so appreciate you continuing to try to help me with this... Unfortunately, I'm still getting "#NO MATCH"

Here's my helper column:

and here's the updated formula:

Paul Newcome

Ok. Try putting that same helper column also in the source sheet and reference that in your formula as well.

vdemattei

IT WORKED!! I cannot thank you enough. I'm so happy to have figured this out - I have several other places I can use this functionality. I really, truly appreciate your time and efforts to help me!

Paul Newcome

Happy to help. 👍️