Pulling data from another sheet based on two criteria

Hello! I am having trouble getting this formula to work..

The goal is to pull the quantity of an item from a reference sheet to this sheet if the Spec #@row on this sheet matches an EAN on the reference sheet, and only if the Disposition value reads "Released".

This is the formula I have cooked up, but get an invalid/incorrect error. Any help is greatly appreciated! TYIA

=INDEX(COLLECT({QUANTITY}, {EAN}, [SPEC #]@row , {disposition}, "RELEASED"))

Find more posts tagged with

Sheets

Formulas

collect

INDEX

Accepted answers

All comments

Soumitra Bhowmick

Change INDEX to SUM if you are sure that the critera will always return 1 row as COLLLECT can only be used in another formula.

Also make sure that {QUANTITY}, {EAN}, {disposition} references are have same row height.

Another way that you can apply is to create a Key in main sheet with cimbination of [SPEC #]@row + "RELEASED". Create a similar key in your reference sheet with [EAN]@row + [disposition]@row. Then use VLOOKUP on these keys.

Paul Newcome

You just need the "row #" argument for the INDEX function.

=INDEX(COLLECT({QUANTITY}, {EAN}, [SPEC #]@row, {disposition}, "RELEASED"), 1)

Paul Newcome

@Soumitra Bhowmick I strongly recommend against creating a special key and then using the VLOOKUP function. The VLOOKUP is very rigid and easy to break. If you really want to use the combined key in a helper column method for a lookup, I suggest an INDEX/MATCH instead of the VLOOKUP.

But in this case, INDEX/COLLECT allows for a lookup using multiple matches so long as you use the "row #" portion of the INDEX function and does not require a helper column.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.