# Return Multiple Matches into Corresponding Table

1. ## Return Multiple Matches into Corresponding Table

Hi there,

I have a table with a number of system options and their associated characteristics (including price).
The last column of my table is a data validation column, with the option to include (options are 'Yes', 'No').

I need to return the system options that have been selected as 'Yes' into a different table, and include their associated cost.
I want to avoid Macros and I've been look at VLOOKUP, INDEX-MATCH and so on but can't figure out how to return multiple matches.

I have a feeling its quite a simple solution. I've attached an example of the problem - thanks in advance for your help!
Kind regards,

P

:-)

2. ## Re: Return Multiple Matches into Corresponding Table

Try

H3=IFERROR(INDEX(Table1[System],SMALL(IF(Table1[Include?]="Yes",ROW(Table1[Include?])-ROW(\$F\$3)),ROWS(\$H\$3:H3))),"") with Control+Shift+Enter and drag down!!

3. ## Re: Return Multiple Matches into Corresponding Table

Try these...

This array formula** entered in H3:

=IFERROR(INDEX(B:B,SMALL(IF(F\$3:F\$10="Yes",ROW(F\$3:F\$10)),ROWS(H\$3:H3))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

This formula entered in I3:

=IF(H3="","",VLOOKUP(H3,B\$3:E\$10,4,0))

Select H3:I3 and copy down until you get blanks.

4. ## Re: Return Multiple Matches into Corresponding Table

Wonderful, thank you very much!!!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1