x-sheet formula help

I am attempting to write a cross sheet formula on sheet (call it sheet a) that returns the value of a column on reference sheet b, based on finding a string of text found in anther column on sheet b that matches a Project ID on sheet a.

Here was attempt 1 that didn't work

=INDEX(PERFORMANCE AMOUNT), MATCH([PROJECT ID]@row, LEFT({PERFORMER ID}, 14)))

(Performance Amount and Performer ID are look-up column from the same reference sheet)

I can't seem to phrase my question well enough for the AI functionality to pick-up what I'm trying to do.

Any thoughts?

Thank you

Find more posts tagged with

Formulas

help

Accepted answers

All comments

VBAGuru

Quite possibly the extra ')' after PERFORMANCE AMOUNT! But then also your reference to this is missing the {}.

It should look like

=INDEX({PERFORMANCE AMOUNT}, MATCH([PROJECT ID]@row, LEFT({PERFORMER ID}, 14)))

VBAGuru

Other issue would be if PROJECT ID is a number. But this is only after fixing the above error!

Smar Czar

Thank you @VBAGuru

with the following formula I now get an #INVALID DATA TYPE error.

=INDEX({Amount}, MATCH([PROJECT ID]@row, LEFT({PERFORMER ID}, 14)))

Project ID for example follows this format: 2.1003.1C.P128

Performer ID as an example would be 2.1003.1C.P128.3.2

Paul Newcome

You will need an INDEX/COLLECT instead.

=INDEX(COLLECT({Amount Column Only}, {Performer ID Column Only}, LEFT(@cell, 14) = [Project ID]@row), 1)

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.