# Need to search array and return value along with title and row values

1. ## Need to search array and return value along with title and row values

I have used the SMALL and LARGE functions to create a bottom and top 10, but I need to include the corresponding row and column names to identify those values. I have attached a sample.

2. ## Re: Need to search array and return value along with title and row values

In J3 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

=IFERROR(INDEX(\$A\$1:\$H\$1,,MIN(IF(\$B\$2:H13=J2,COLUMN(\$B\$2:\$H\$13)))),"")

In J7 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

=IFERROR(INDEX(\$A\$1:\$H\$1,,MIN(IF(\$B\$2:H13=J6,COLUMN(\$B\$2:\$H\$13)))),"")

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.

Drag both the formula's to right...

3. ## Re: Need to search array and return value along with title and row values

Hi wilburr,

one more approach...
In J3 use formula as..

Formula:
`Please Login or Register  to view this content.`

format the cell as "DD-MM-YYYY HH:MM"

and copy the same in J7 too.. or drag rightward.

PS: change A2 to 0 for better view..

4. ## Re: Need to search array and return value along with title and row values

@ Debraj Roy,

The reason for avoiding sumproduct approach is it will Fail/Throw an Error if there is multiple matches. Because sumproduct accumulates all the matches

5. ## Re: Need to search array and return value along with title and row values

@ :) Sixthsense :)

I agree boss... :)
but for 2D search.. I know only this method.. need some time to learn more.. ;(

Keep guiding..

6. ## Re: Need to search array and return value along with title and row values

In fact me too thought to go for the same approach which you shown in Post #3, but because of possibility of duplicate records I need to go for Array solution

I just wanted to let you know the duplicate risk if you are not aware.

Thanks for taking it in a positive way

7. ## Re: Need to search array and return value along with title and row values

I used the first formula provided and it resulted in a number. I used the comments from the second responder to format the cell and it almost worked.

Two questions.
1. how do I format the cell to show the results desired in the attached/
2. Would you walk me through the first formula so I understand it better and do not need to ask the question again when working on a larger spreadsheet containing multiple weeks.

TIA

Wilburr

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