Index and match Questions

Alex Klingbeil

looking for help with my Index and match problem I am having

=INDEX({Nelson Cable List Inventory Install Tracki Range 1}, MATCH([Spool ID]@row, {Nelson Cable List Inventory Install Tracki Range 2}, 0))

Range 1 is the Data I want to Show on the new sheet.

Range 2 is the Data I am Matching on new sheet from the other sheet.

on the second sheet I am trying to grab the information from uses a Drop down list. and it will have anywhere from 1-4 things listed in a cell.

Example:

"108-G1

108-G2

108-G3"

but the Formula only works when its just a Single item like 108-G1.

with more then a single item in the cell it will just say #NO MATCH.

also is there a way to Write an Index and match that will Add numbers if something shows up in Multiple cells.

Wire Length Spool ID

275' 108-G1

300' 110-A

250' 108-G1

200' 108-G1

how would you get it to say I need to have 108-G1 at a length of 725'

I hope I explained this all right so everyone understand what I am trying to ask.

Paul Newcome

You are going to need to switch over to an INDEX/COLLECT and incorporate a HAS function.

=INDEX(COLLECT({Nelson Cable List Inventory Install Tracki Range 1}, {Nelson Cable List Inventory Install Tracki Range 2}, HAS(@cell, [Spool ID]@row)), 1)

For your second question you will need to use a SUMIFS.

Paul Newcome

You are going to need to switch over to an INDEX/COLLECT and incorporate a HAS function.

=INDEX(COLLECT({Nelson Cable List Inventory Install Tracki Range 1}, {Nelson Cable List Inventory Install Tracki Range 2}, HAS(@cell, [Spool ID]@row)), 1)

For your second question you will need to use a SUMIFS.