Lookup a value in one array, and return a corresponding column/row in another array

**Gunther Maplethorpe** · 11-14-2012, 05:45 PM

I'm sure this is going to end up being fairly easy, but I've been struggling for quite some time now and can't get this to work.

Given two arrays of cells: one has a set of unique strings (along with some blank cells), and the other has a set of numeric values (not necessarily unique). I'd like a separate columne to lookup/index/match (whichever works) the values in the first array, and return the correspondingly-positioned cell in the second array.

A screenshot example of what I'm looking for:
correxample.png

...and a fuller, more representative sample in worksheet form.
96plate_correspondence.xlsx

**Ace_XL** · 11-14-2012, 06:13 PM

Try this array formula..

In c28
=INDIRECT(ADDRESS(MAX(IF($B$4:$M$11=B28,ROW($B$4:$M$11)))+11,MAX(IF($B$4:$M$11=B28,COLUMN($B$4:$M$11))),4))

Confirm with Ctrl+Shift+Enter and not just Enter

**Gunther Maplethorpe** · 11-14-2012, 06:29 PM

That seems to be working a treat, Ace_XL! It'll take me a while to understand it, but for the moment, I can use it for what I need. Muchas gracis.

**icestationzbra** · 11-15-2012, 08:47 PM

since your first matrix is unique, you could use this non-volatile regular formula:

Please Login or Register  to view this content.

and drag-fill down.

Lookup a value in one array, and return a corresponding column/row in another array

LinkBack

Thread Tools

Rate This Thread

Display

Lookup a value in one array, and return a corresponding column/row in another array

Re: Lookup a value in one array, and return a corresponding column/row in another array

Re: Lookup a value in one array, and return a corresponding column/row in another array

Re: Lookup a value in one array, and return a corresponding column/row in another array

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions