Identify column to index based on match value [SOLVED]

[clapprp]

I'm tyring to complete a task but I'm struggling with identifying a formula that will specify a lookup array given a specific value. Below is a mock dataset. What I'm trying to complete is based on a value I'll uase as a reference (let's say "3" in this case) I would use column C in the MATCH function example and my lookup_array would be C1:C4. Because the syntax of the MATCH function requires I specify my lookup_array and my lookup_array is unknown, I am having to manually change the array to be the column i'm trying to use.


A B C D

1 a b b b

2 b a c c

3 c c a c

4 d d d a


Any idea how to solve this?

 

[Luke M]

The INDEX function is exactly what you are looking for. Something like this:

=MATCH(MyValue,INDEX(A1:D4,,3),0)


will search for MyValue within the 3rd column of range given in the INDEX function (in this case, C1:C4). You can make the last argument ("3" in the example) to be a cell reference to make things more dynamic.


PS. This forum is in the middle of migration. All posts made after 10:00 am EST on Friday will be lost after migration.

 

[clapprp]

This is great. I tweaked it so I could make it dynamic based on lookup_value. Thanks for your help!