Index, Match with a Contains.

Michael Foster

Hi,

I normally index match to return a value from a table but this only works for the match section if it is a 1 for 1 scenario. The scenario i currently have is the reference table which i'm indexing from for the field i am using as a match has additional information (See Table 2). How do i collect the information from Table 2 if it has additional information. I have tried to introduce a contains in but i get an error.

Thanks in advance for your help.

Dale Murphy

@Michael Foster Are you trying to return information from two (or more) columns? Or are you trying to narrow what is returned by comparing more than one column? (Sorry, I can't tell exactly what your objective is.)

dm

Michael Foster

@Dale Murphy I'm trying to Collect "Mr Smith" from Table 2 to Table 1, I only have a partial match as table 1 only has "FC" but table 2 shows "FC / DC". I want the formula to use "FC" from table 1 code field and see if it is contained in table 2 code field. If it is it should bring back the data from Table 2 Index Match to Table 1 Collect information.

Thanks

MF

Paul Newcome

Try looking into an INDEX/COLLECT combo with a CONTAINS.

=INDEX(COLLECT({Range to pull}, {Range to Match}, CONTAINS(@cell, Code@row)), 1)