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

	A​	B​	C​	D​	E​	F​
1​	Search​
2​	10​	1​	john​
3​	Result​		4​	mike​
4​	mary​		10​	mary​
5​				30​	richard​
6​			60​	mark​
7​				50​	robert​
8​		0​	1​	0​	0​
9​
10​

<tbody>
</tbody>

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!

0	Dog	40	Salmon	41	Pickles
1	Cat
2	Mouse	43	Juice
3	Fox
4	Cheese
5	Bacon

<tbody>
</tbody>

 

[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

	A​	B​	C​	D​	E​	F​	G​
1​	Search​
2​	43​	0​	Dog​	40​	Salmon​	41​	Pickles​
3​	Result​	1​	Cat​
4​	Juice​	2​	Mouse​	43​	Juice​
5​		3​	Fox​
6​		4​	Cheese​
7​		5​	Bacon​
8​		0​	0​	1​	0​	0​	0​
9​
10​

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.