Looking up values from a 2D array instead of just a column

  • Thread starter Thread starter Gamesmith
  • Start date Start date
G

Gamesmith

Can someone suggest how one might look up a value from a two-dimensiona
array (such as, say, B2:F30), then return a value from a specifie
column that shares a row with the searched value?

For example, if I had data that looked like this:

Apple 3 5 1
Orange 2 9
Grapefruit 6 4

I would like the search term "5" to return "Apple" and the search ter
"6" to return "Grapefruit".


Why would I want to do this? Here's the mission:

I am tracking several sub-tasks that have been organized into projects
One sheet lists the tasks, another lists the projects. My goal is to b
able to assign (or reassign) tasks (using their ID numbers) t
projects, and have that assignment automatically update a "project
field.

So, on the task list, I might have something that looks like this:

ID TASK PROJEC
1 Install outlets (to be looked up)
2 Paint exterior (to be looked up)
3 Replace Romex (to be looked up)

And on the Project list, I might have something that looks like this:

PROJECT TASKS INCLUDE
Electrical 1 3
Painting 2

So, if I change what project a task is assigned to on the Project list
the Task list would automatically update appropriately.

Thanks in advance for reading all this crap and giving me som
suggestions
 
One way

=INDEX(A1:A4,MAX(($B$2:$D$4=9)*(ROW(B2:D4))))

entered with ctrl shift & enter

note that the index formula starts from the first row, that is because if
you include the row
function in this way it will ibdex from the first row, so if you table
starts in A10 use this formula


=INDEX(A1:A12,MAX(($B$10:$D$12=9)*(ROW(B10:D12))))
 
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, then with
the value to be matched in Cell K1:

=INDEX(datarange,ColumnVector(ArrayMatch(K1,datarange),1),1)

Alan Beban
 
If the numbers are unique, then the formula I provided and that of Peo
Sjoblom return the same result. If they are not, the formula I provided
returns the 1st column result corresponding to the first occurrence of
the sought number, and the one Peo Sjoblom provided returns the 1st
column result corresponding to the last occurrence of the sought number.

Alan Beban
 
Excellent suggestions, Peo and Alan!

The values in my circumstance are unique, so I was able to use Peo'
formula.

Thanks to you guys, I retain my local title of "Resident Excel Weenie.
What can I say, I work with people who are easily impressed
 
Back
Top