Find cell in array, return value of cell next to it

**ryanfrank** · 10-23-2009, 01:29 AM

I have a table with multiple columns in which I want to find a value, then return the value that shows up to the right of it.

For instance:

I want to look for a unique value that exists in a cell somewhere in columns B-F, then I want to return the value of the cell in the next column.

So if I have value XYZ, and it's found in row 12 of Column B, I want to return the value in row 12 of column C.

Any way to do this with an index and match or some other formula?

**DonkeyOte** · 10-23-2009, 02:19 AM

Generally speaking I like to break this type of thing down to a two part calculation for simplicity, eg:

A1: =MIN(IF(B1:F100="XYZ",ROW(B1:F100)+COLUMN(B1:F100)/10000))
Confirmed with CTRL + SHIFT + ENTER
alter ranges as required but when using Arrays its imperative to keep streamlined (ie keep ranges as small as possible)
pre XL2007 you can not use entire column references (ie F:F)

Then use the above "co-ordinates" for extraction

A2: =IF(A1,INDEX(A1:G100,INT(A1),1+(MOD(A1,1)*10000)),"")

**ryanfrank** · 10-24-2009, 12:40 AM

What if the value in the cell isn't a number, but is an alpha code. Will that still work?

**DonkeyOte** · 10-24-2009, 01:41 AM

Yes. Always best to test.

to qualify the above...

A1 returns co-ordinates of first match (data type of criteria irrelevant) - "first" being determined by row and then column should multiple matches exist
A2 returns the content of the cell identified by co-ordinates in A1 (data type of cell content not important - of course if error it will return error also)

**ryanfrank** · 10-26-2009, 01:25 AM

Thanks so much for the help. I truly appreciate it.

I used the formula above for extracting the coordinates, but I can't seem to get it to work.

The value I want to find the coordinates for is in cell A25. The range or array is on another worksheet. Is that making the difference?

Here's my entry. It returns the #VALUE error.

=MIN(IF(IndustryDriver!B:AN=A25,ROW(IndustryDriver!B:AN)+COLUMN(IndustryDriver!B:AN)/10000))

**ryanfrank** · 10-26-2009, 01:26 AM

and I was sure to use CTRL+Shift+Enter.

**JeanRage** · 10-26-2009, 03:02 AM

Hi,

Ideally could you post, as an attachment, a sample portion of your file ?

HTH

**DonkeyOte** · 10-26-2009, 04:20 AM

I used the formula above for extracting the coordinates, but I can't seem to get it to work.

see post # 2 and specifically the text in Italics...

**ryanfrank** · 10-26-2009, 04:37 PM

I see, the columns were the issue. I've attached sample data this time. Again, I appreciate the help here.

So I've got the first formula working, but the second formula is pulling the value from the next row and two columns down. See sample data.

It should return the value from the same row and the next column. In the sample data, it should be MWW.

**DonkeyOte** · 10-26-2009, 04:50 PM

Given you are returning an unadjusted row/column reference your index must commence from A1
ie compare my original INDEX range to the one you're using