# Search for a value in a row, return list of cells below that particular cell

1. ## Search for a value in a row, return list of cells below that particular cell

Hi, can someone help me please?

excel1.PNG

I want to be able to search for a particular value in row E7:H7 and have it return the cells below as indicated on the picture attached.

HOWEVER, if I search for "B" I don't get 2,6,wait,14,18 returned because I don't know how to extend the look up value.

This is the formula I am using: =INDEX(E7:H12,MATCH(\$J\$8,\$E\$7:\$E\$12,0)+{1,2,3,4,5},1)

Can anyone help me adjust this formula so that I can change the search value to B or C and get the following list of cells below them?

(Hope I am making myself clear enough)

2. ## Re: Search for a value in a row, return list of cells below that particular cell

Select K8:K12
Enter =INDEX(\$E\$7:\$H\$12,,MATCH(\$J\$8,\$E\$7:\$H\$7,0)) in the formula bar
Then commit with Ctrl+Shift+Enter (array formula) - Results appear in the selected range
Once this is done change J8 as needed

3. ## Re: Search for a value in a row, return list of cells below that particular cell

Doesn't work

Attachment 637937

4. ## Re: Search for a value in a row, return list of cells below that particular cell

Maybe put this on blank cells then ENTERED as array formula, press all together CTRL+SHIFT+ENTER button then copied down as necessary:

=IFERROR(INDEX(\$E\$8:\$H\$12,SMALL(IF((INDEX(\$E\$8:\$H\$12,0,MATCH(\$J\$8,\$E\$7:\$H\$7,0))<>""),MATCH(ROW(\$A\$8:\$A\$12),ROW(\$A\$8:\$A\$12)),""),ROW(A1)),MATCH(\$J\$8,\$E\$7:\$H\$7,0)),"")

5. ## Re: Search for a value in a row, return list of cells below that particular cell

Originally Posted by azumi
Maybe put this on blank cells then ENTERED as array formula, press all together CTRL+SHIFT+ENTER button then copied down as necessary:

=IFERROR(INDEX(\$E\$8:\$H\$12,SMALL(IF((INDEX(\$E\$8:\$H\$12,0,MATCH(\$J\$8,\$E\$7:\$H\$7,0))<>""),MATCH(ROW(\$A\$8:\$A\$12),ROW(\$A\$8:\$A\$12)),""),ROW(A1)),MATCH(\$J\$8,\$E\$7:\$H\$7,0)),"")
Thanks so much! it worked.

6. ## Re: Search for a value in a row, return list of cells below that particular cell

Originally Posted by fendersony
Doesn't work

Attachment 637937
Please explain what "doesn't work" means.
• Does the code run?
• Does it run but do nothing?
• Does it produce error messages? If so, what do the messages say?
• Does it produce unexpected/wrong results? If so, how do the results differ from what you expect?
• Does it hang?

It works perfectly if you follow the instructions

##### Users Browsing this Thread

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