INDEX COLLECT formula

REmley

I am having some issues with the index collect. I am trying to return data from one sheet looking at two columns from another. I have watched multiple videos on this and I cannot seem to get it right.

Currently I have: =INDEX(COLLECT({Asset ID}, {Maker Codes}, [Maker Line]@row, {Packer Code}, [Packer Line]@row, 0))

Asset ID is on sheet 2 column 3

Maker Codes is on sheet 2 column 1 rows 1-8

Maker Line is on sheet 1 which user inputs via form

Packer Code is on sheet 2 column 1 rows 9-16

Packer Line is on sheet 1 which user inputs via form

I can get this to work using index match but I need it to look at both columns on sheet 1 as it is going to be either a Maker Line or Packer Line. =INDEX({Asset ID Code}, MATCH([Maker Line]@row, {Maker Code}, 0))

Sheet 2---^

Sheet 1---^

Ryan Sides

Hi @REmley ,

I can't tell from your example where the Packer Line is referenced in Sheet 2, or how you are expecting these to match. Can you give another example with screenshots of them matching? And in which field you need your index/collect to go?

Thanks, Ryan

REmley

=INDEX(COLLECT({Asset ID}, {Maker Codes}, [Maker Line]@row, {Packer Code}, [Packer Line]@row, 0))

I have selected rows 1-8 for Maker Code on sheet 2 and selected rows 9-16 for Packer Code.

We have a form that gives the option of Maker, Packer or Other. If maker is selected they get codes A-H and if packer is selected they get codes 1-8. Depending on what they sheet 1 will get filled in the corresponding column. What I am trying to do is use index collect to recall the Asset ID while searching in both Maker Line and Packer Line.

=INDEX({Asset ID Code}, MATCH([Maker Line]@row, {Maker Code}, 0))

Works fine and will recall the Asset ID but it is only based on the Maker Line and if they chose a Packer Line nothing will be recalled. Does that make sense?

Dale Murphy

@REmley I would probably check if the Maker Line field is blank, and if it is check the Packer code. something like:

=if(ISBLANK({packer code}), (INDEX({Asset ID Code}, MATCH([Maker Line]@row, {Maker Code}, 0))), (INDEX({Asset ID Code}, MATCH([Packer Line]@row, {Packer Code}, 0))))

(Sorry, haven't tested this across two sheets, but I think the logic is okay.)

dm

REmley

@Dale Murphy - Thank you for the input, however, I was still unable to get it to work with your suggestion. I final got the formula to work as I had the syntax wrong at the ending.

=INDEX(COLLECT({Asset ID}, {Maker Codes}, [Maker Line]@row, {Packer Code}, [Packer Line]@row, 0))

The ending syntax should of looked like [Packer Line]@row), 1)

Sorry for the late response, I was out for a surgery!