x-sheet formula help

Smar Czar

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

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)