Using IF AND VLOOKUP

Pam Dunn

I want to cross reference two sheets to determine that the ItemNumbers are listed on both documents (Missing Images & Placeholder) and meets the condition Done is unchecked on Placeholder doc. I exported to excel did a VLOOKUP and had 317 hits. When I do this in Smartsheet it is not providing the same # of items that match, the total is only 175 rows that match.

I am using this formula in the Placeholder document

=IF(AND(VLOOKUP(ItemNumber@row, {MissingItem}, 1) = ItemNumber@row, Done@row = 0), "NO ACTION", "")

1st Sheet: Missing Images

2nd Sheet: Placeholder Images

In the image below the criteria is met, but the NO ACTION is not appearing for this Items. I manually cross referenced and these appear on both documents.

Thank you for assistance you can provide

Dale Murphy

@Pam Dunn (I would recommend exploring the INDEX function to replace VLOOKUP.)

I would start by building a third sheet to hold results of your [manual or automated] cross references. (You may have done this already).

Create the cross-reference formula that builds a full list of ItemNumbers as they seem key. Then have other columns, some from each of the two sheets. so you can see all the key elements you are looking to reference. (I need the visualization to understand the data.)

Once you see all the key elements you should be able to count(?) when your various conditions are or are not met.

dm

Dale Murphy

@Pam Dunn (I would recommend exploring the INDEX function to replace VLOOKUP.)

I would start by building a third sheet to hold results of your [manual or automated] cross references. (You may have done this already).

Create the cross-reference formula that builds a full list of ItemNumbers as they seem key. Then have other columns, some from each of the two sheets. so you can see all the key elements you are looking to reference. (I need the visualization to understand the data.)

Once you see all the key elements you should be able to count(?) when your various conditions are or are not met.

dm

Pam Dunn

@Dale Murphy Dale I was able to use the INDEX and COLLECT functions to make it work. Thank you