Index and Match or VLookup or both

Darla Brown

I am trying to recreate this excel formula in smartsheet:

=IF(ISNA(VLOOKUP(B2,'Participating Hotels'!$B:$B,2,FALSE)),"Not Available","Available")

• "B2" is the Property ID column on my sheet where the results will sit. So it will be ID@row
• Participating Hotels $B:$B is the base sheet column where all of the data sits. So this would be ID:ID, of course linked to the other sheet.
• Instead of 2, it would need to be 1 because the ID column in Smartsheet is column 1.

Paul Newcome

That's an odd error to be getting. Hmm...

=IF(COUNTIFS({Other Sheet ID Column}, ID@row) = 0, "Unavailable", "Available")

What happens with that one?

Paul Newcome

Can you show a screenshot of each sheet with sensitive/confidential data removed, blocked, and/or replaced with "dummy data" as needed?

Darla Brown

I have this formula now just so I can verify it is available, but would like for it to say "Available" or Unavailable".

My second step would be to show Chain segment and Property Type data from the Benchmarker list since properties will update those.

Paul Newcome

What determine if it is "Available" or Unavailable"?

Darla Brown

If that ID is also found on the Benchmarker List.

Paul Newcome

Ok. Try something like this then...

=IF(CONTAINS(ID@row, {Other Sheet ID Column}), "Available", "Unavailable")

Darla Brown

It is defaulting to Unavailable for all even though they are there. hmmm.

Paul Newcome

How is the ID entered on each of the sheets?

Darla Brown

Both sheets were imported from excel. Benchmarker List is this year's list. The 2nd sheet is last year's list I need to update. So instead of manually searching, I wanted to do a formula. The VLookup I had originally, just returned the ID which told me they were available. So then I would have to manually type over the ID to say "Available" or "Unavailable. They are both Text/Number formatted columns.

Paul Newcome

Ok. Lets try a FIND function instead...

=IF(FIND(ID@row, {Other Sheet ID Column}) > 0, "Available", "Unavailable")

Darla Brown

Returns #INVALID DATA TYPE

Paul Newcome

Is that error present anywhere within the {Other Sheet ID Column} range?

Darla Brown

No. Just the data.

Paul Newcome

That's an odd error to be getting. Hmm...

=IF(COUNTIFS({Other Sheet ID Column}, ID@row) = 0, "Unavailable", "Available")

What happens with that one?

Darla Brown

That worked!