Find text in array & return adjacent cell value

[econbizer]

Dear all, I'm pretty muddled with this & would appreciate your help!

I'm looking for a cell reference solution to finding any "text/number" in a given array & returning an adjacent cell value.
The adjacent cell being called should be dynamic.. could be (3 rows down, 1 right) or even (5 rows down, 6 right).

For eg, cell B2 will contain the formula, to find the text "Syllabus e01932 summer" in my given array & then returning its adjacent cell value, "26" (3 rows down, 1 column right).

Please see the attached screen shot/workbook!
I wish to solve this using cell formulas & not vba macros.


Thank you,
eb

Find text in array return adjacent cell value.JPG
Find text in array return adjacent cell value.xls

[scottylad2]

You have the search criteria in F4, is that always going to be the case or could that beanywhere in the array? will the cell you wish returned always be at the same offset? A little more explanation is required, also will the lookup taxt always be the same or are you hoping to enter that each time into B2?

[econbizer]

You have the search criteria in F4, is that always going to be the case or could that beanywhere in the array? will the cell you wish returned always be at the same offset? A little more explanation is required, also will the lookup taxt always be the same or are you hoping to enter that each time into B2?

Dear scottylad2,

You have hit my problem spot on! Very sharp!

Yes it has to be dynamic such that we can search for any text in the array & obtain an adjacent cell value.
For example, we could instead search for "Charts" to return an adjacent cell offset (1,2) to return "screen".
Or we could search for "165" to return an adjacent cell offset (0,2) to return "364".

Thank you

[Pete_UK]

Put the text you wish to find in B1 and this formula in B2:

=INDEX(G:G,MATCH(B1,F:F,0)+3)

This will return an error if the text is not found - you can avoid the error like this:

=IF(ISNA(MATCH(B1,F:F,0)),"",INDEX(G:G,MATCH(B1,F:F,0)+3))

Hope this helps.

Pete

[econbizer]

bump.. any suggestions on this?

[zbor]

Before I even a read a post please upload example workbook rather then a picture.
I'm very bad at drawing

[econbizer]

Before I even a read a post please upload example workbook rather then a picture.
I'm very bad at drawing

Hi zbor, thanks for replying..
I've attached the workbook.

I'm looking for a cell reference solution to finding any "text/number" in a given array & returning an adjacent cell value.
The adjacent cell being called should be dynamic.. could be (3 rows down, 1 right) or even (5 rows down, 6 right).

Thank you,
eb

Find text in array return adjacent cell value.xls

[zbor]

Maybe this:

=INDEX($B$5:$H$9,3,1+MATCH("Syllabus e01932 summer",$B$4:$H$4,0))

[econbizer]

Maybe this:

=INDEX($B$5:$H$9,3,1+MATCH("Syllabus e01932 summer",$B$4:$H$4,0))

Hi zbor, thanks for your suggestion.
The formula should be able to find anything within the set array.


B4:H9 is a set array in the .xls example, and there should be no fixed headers.
ie. if I were to search for "73" to return an adjacent cell (2,1), it will return "634".



Thank you,
eb

[zbor]

How do you define what you looking and where is start of your array?

[WHER]

This is how i read the question:

[econbizer]

This is how i read the question:

Awesome
Thanks WHER, a ONE step solution.. trademark of a cool programmer!
Thanks also to your replies guys!


Thank you,
eb

[Zach8384]

Hi WHER,

I notice that you are good in this hence I am here to seek your assistance. What I'm trying to do is, to search for column "K" values in the array of column "E", and produce the based in column "F". However, if the value in column "F" is "ACTIVE", I would then want to produce the result in column "G" instead. If the value is not found in column "E", it will then produce a result of "CHECK".

Please assist.

[protonLeah]

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.