Large array lookup

[hartjonathand]

You guys were super helpful on my last issue. I am trying (without defining a new function in VBA) to do a lookup where I have numbers (1-60) in multiple rows and columns (the total range is B2:AC46). In every case the name I need is next to a number so I know what I need to reference, but I don't know how to search the whole array and return whatever is adjacent to that cell. So for instance the number 40 could be in B2, so I would need C2, or it could be in Y50 so I would return Z50. Does that make sense? I would like to not use any volatile functions here as the workbook is already very computationally intensive.

 

[Marcelo Branco]

Many restrictions - no VBA, no array formulas, non volatile functions.
Would an auxiliary row with a simple formula be acceptable?

M.

 

[hartjonathand]

Unlikely. Sounds like I should just write my own function in VBA, it seems like that might be my only option, but I thought it might be simple enough to just search the whole array, find the reference number and return the adjacent value, it would seem obvious that vlookup/index match, etc wouldn't work for this?

 

[Marcelo Branco]

MATCH works only with one-dimension array/range.

M.

 

[hartjonathand]

so is there really no defined functions that can allow you to search a large array that isn't sorted and is mxn where m^n > 2 while return an adjacent value?

 

[hartjonathand]

also https://www.excelforum.com/excel-fo...ay-and-return-adjacent-value.html#post4810249 in case anyone is online over there.

 

[Marcelo Branco]

My suggestion

Example with a small data sample

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Search​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

10​

[/TD]
[TD="bgcolor: #D9D9D9"]

1​

[/TD]
[TD="bgcolor: #D9D9D9"]

john​

[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Result​

[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]

4​

[/TD]
[TD="bgcolor: #D9D9D9"]

mike​

[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

mary​

[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]

10​

[/TD]
[TD="bgcolor: #D9D9D9"]

mary​

[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]

30​

[/TD]
[TD="bgcolor: #D9D9D9"]

richard​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]

60​

[/TD]
[TD="bgcolor: #D9D9D9"]

mark​

[/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"][/TD]
[TD="bgcolor: #D9D9D9"]

50​

[/TD]
[TD="bgcolor: #D9D9D9"]

robert​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD="bgcolor: #DCE6F1"]

0​

[/TD]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD="bgcolor: #DCE6F1"]

0​

[/TD]
[TD="bgcolor: #DCE6F1"]

0​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Data in B2:E7 (gray area)

Helper row (blue area)
Formula in B8 copied across
=--ISNUMBER(MATCH($A$2,B$2:B$7,0))

Formula in A2
=INDEX(B2:E7,MATCH(A2,INDEX(B2:E7,0,MATCH(1,B8:E8,0)),0),MATCH(1,B8:E8,0)+1)

Very simple and efficient formulas

M.

 

[hartjonathand]

I am including an example below, but I need to be able to return juice when I reference 43, bacon if 5 is my reference, pickles if 41 is my reference and so on. Thanks!
[TABLE="width: 500"]
<tbody>[TR]
[TD]0[/TD]
[TD]Dog[/TD]
[TD]40[/TD]
[TD]Salmon[/TD]
[TD]41[/TD]
[TD]Pickles[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Cat[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Mouse[/TD]
[TD]43[/TD]
[TD]Juice[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Fox[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Cheese[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Bacon[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

I'd stick with Marcelo's method, it requires a helper row, but checks off all of your other requirements. I came up with a single cell formula:

A4: =IFERROR(INDIRECT(TEXT(MIN(IF($B$2:$E$7=A2,ROW($B$2:$E$7)*100+COLUMN($B$2:$E$7)+1)),"R00C00"),0),"")

but it's an array formula and volatile.

 

[Marcelo Branco]

Using your data sample in post 8


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Search​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

43​

[/td][td]

0​

[/td][td]

Dog​

[/td][td]

40​

[/td][td]

Salmon​

[/td][td]

41​

[/td][td]

Pickles​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Result​

[/td][td]

1​

[/td][td]

Cat​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Juice​

[/td][td]

2​

[/td][td]

Mouse​

[/td][td]

43​

[/td][td]

Juice​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td]

3​

[/td][td]

Fox​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td]

4​

[/td][td]

Cheese​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td]

5​

[/td][td]

Bacon​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td]

0​

[/td][td]

0​

[/td][td]

1​

[/td][td]

0​

[/td][td]

0​

[/td][td]

0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in B8 copied across
=--ISNUMBER(MATCH($A$2,B$2:B$7,0))

Formula in A4
=INDEX(B2:G7,MATCH(A2,INDEX(B2:G7,0,MATCH(1,B8:G8,0)),0),MATCH(1,B8:G8,0)+1)

M.