returning a cell address from an array

[hannah2233]

Hi Guys,
I have an array as follows

-2 b
-1 c
0 d
1 e
2 f

I can easily do this using vlookup(a3,a1:b5,2,False). However the problem is the value I want could be anywhere in the table so I need another function to call out the cell location (a3) before I use vlookup. I have tried variations of using address and indirect but nothing.

Thanks in advance

<3 Hannah xx

 

[RoryA]

How do you determine what the value to find is?

I suspect you actually want INDEX and MATCH but it's not really clear to me currently.

=INDEX(B1:B5,match(some_value,A1:A5,0))

 

[hannah2233]

Hi Rory,

This works but the problem is I need a function to call out the cell location of "some_value" for two reasons.
1. I do not know the location of it within the array so I need a function to find it
2. I need to be able to increment "some_value" with the next cell so the value of "some_value" will change.

Does this help?

Thanks
Han x

 

[RoryA]

Not really. The MATCH function locates some_value in column A. INDEX then returns the value from column B of the same row. I'm not clear on how you determine what some_value is or what exactly you are trying to achieve - can you give more specific examples of what you need?

 

[hannah2233]

So I'll try give you an example. For the table

-2 A
-1 B
0 C
1 D
2 E

In cell D1 I want to call out the corresponding value to 0 in the cell ie/C
In cell D2 I want to call out the corresponding value to 1 in the cell ie/D
In cell D3 I want to call out the corresponding value to 2 in the cell ie/E
etc, etc

=INDEX(B1:B5,match(some_value,A1:A5,0)) works great However, I dont know where is "some_value" it may be located in any cell. So I need another function to first return the cell address of "some_value" to then use the index function.

Hope that makes sense

Thanks
Han x

 

[RoryA]

You lost me at the end. The example you gave could just be:
=INDEX($B$1:$B$5,match(ROW()-1,$A$1:$A$5,0))
in D1 and fill down as far as necessary.