Find Value in Array and return value in cell to right

[Ashtangi]

Surley this is easy but i cant figure it out.

i want to search for A16 in B2:I10 and return the date 1 cell to the right.

[Saarang84]

Hi,

There are 3 date columns found in your sheet, which one do you want to return?

[hemesh]

copy paste below in then hold control and shift together and then hit enter to make it array formula
=INDIRECT(TEXT(MAX(IFERROR(IF(SEARCH($A16,$A$2:$I$9)>=1,ROW($A$2:$I$9)*1000+COLUMN($A$2:$I$9)+1),0)),"R000C000"),0)

format b16 in date format.

this will work as long as you don't have duplicate current site

[Ashtangi]

thanks hemesh, what if you wanted to return the rig name and the date?

[Ashtangi]

Saarang,

the date 1 cell to the right of the cell that COM241 is in

thanks

[Saarang84]

Try this to get the dates :

Please Login or Register  to view this content.

This works in Excel 2003 too..

[hemesh]

In C16 copy paste below then hold control and shift together then hit enter
=INDIRECT(TEXT(MAX(IFERROR(IF(SEARCH($A16,$A$2:$I$9)>=1,ROW($A$2:$I$9)*1000+COLUMN($A$2)),0)),"R000C000"),0)

[Ashtangi]

how about returning the rig name and the date?

[hemesh]

Above formula will get you the rig name Or you want both in same cell

if you want both in same cell then copy paste below in c16 then hold control and shift together then hit enter
=INDIRECT(TEXT(MAX(IFERROR(IF(SEARCH($A16,$A$2:$I$9)>=1,ROW($A$2:$I$9)*1000+COLUMN($A$2)),0)),"R000C000"),0)&"-----"&TEXT(INDIRECT(TEXT(MAX(IFERROR(IF(SEARCH($A16,$A$2:$I$9)>=1,ROW($A$2:$I$9)*1000+COLUMN($A$2:$I$9)+1),0)),"R000C000"),0),"DDDD, MMMM,DD,YYYY")


Hope this helps

[Saarang84]

Hi,

For Rig Name, use :

Please Login or Register  to view this content.

For Date, use :

Please Login or Register  to view this content.

Refer attached file..

[Ashtangi]

your very clever,

what if the rig name and the date had to be in the same cell, without concatenating

[hemesh]

can you show example Ashtangi and To whom you are responding?

[Ashtangi]

i reply to everyone

desired outcome would be in a single cell

RIG Arrives DATE

eg.

SAV 064 Arrives Monday, 5 May 2014

[hemesh]

try below hold control and shift then hit enter
=INDIRECT(TEXT(MAX(IFERROR(IF(SEARCH($A16,$A$2:$I$9)>=1,ROW($A$2:$I$9)*1000+COLUMN($A$2)),0)),"R000C000"),0)&" Arrives "&TEXT(INDIRECT(TEXT(MAX(IFERROR(IF(SEARCH($A16,$A$2:$I$9)>=1,ROW($A$2:$I$9)*1000+COLUMN($A$2:$I$9)+1),0)),"R000C000"),0),"DDDD, MMMM DD YYYY")

[Ashtangi]

hi hemesh, if it cant find the COM241 number for example it says "N?A" can this be replaced with TEXT??

[hemesh]

what sort of text ?

Try below if search term is not available then it will Show "Search term not Available"
=IFERROR(INDIRECT(TEXT(MAX(IFERROR(IF(SEARCH($A16,$A$2:$I$9)>=1,ROW($A$2:$I$9)*1000+COLUMN($A$2)),0)),"R000C000"),0)&" Arrives "&TEXT(INDIRECT(TEXT(MAX(IFERROR(IF(SEARCH($A16,$A$2:$I$9)>=1,ROW($A$2:$I$9)*1000+COLUMN($A$2:$I$9)+1),0)),"R000C000"),0),"DDDD, MMMM D YYYY"),"Search Term Not Avalible")

Above formula is array entered. Change the Red Part to your desired Text.

[Ashtangi]

last one,

if the formula is on another sheet and wants to look for the data on a sheet called "Rigs"?

[Saarang84]

Try this Formula :

Please Login or Register  to view this content.

In the above fomula,
1. Change the Red Part to your desired Text
2. This is an array formula. Confirm it by pressing Ctrl + Shift + Enter
3. $A16 (in blue) is the input cell - change it accordingly
4. $A$2:$I$9 is the range where the value searched for is available. As you say, this is what is available in the Rigs sheet

[hemesh]

try below on another sheet array entered (Control+Shift+ENter)

Please Login or Register  to view this content.

Blue part is the sheet name.

Change the references as of your actual data. Try Above Formula in B16 in another sheet and in A16 write the site name

Hope this solves your query

[hemesh]

If your issue is solved you can mark thread as solved.
you could also use if construct instead of search as well

[Ashtangi]

yes thank you kindly, that became quite complex.

[hemesh]

is your issue resolved?

[Ashtangi]

yes i cant see how to close it though?

[hemesh]

go to thread tools then select as solved or Read signature below my post or Read Forum rules.