When using match function can your lookup array be multiple rows and columns

or is it restricted such that the lookup array can only be one column ie: A:A when finding row number or one row ie: 1:1 when finding column number

I'm using the index and match functions in combination and I want the lookup array in the match functions to be the same as the lookup array in the index function.


IE: lets say I want to search vertically for a list of work activity codes and I want to search horizontally for various metrics such as work accomplished, actual hours spent and so on.

but I don't want to always be restricted to having the raw data I'm searching in the same format where activity codes are always column B and the status/metrics on those activity codes is always row 4.



I want to be able to search an entire array of multiple rows and columns (or an entire spreadsheet) for a specific reference and have it return the row number that reference is in.

then I want to be able to search an entire array of multiple rows and columns (or an entire spreadsheet) for a another different specific reference, like hours spent, and then return the column number that reference is in

then i want to have the value returned that occurs at the intersection of that row and column either using the index function or some other function


I tried something like:

=Index(A1:AR90, Match(AC312, A1:AR90, 0), Match("Hours Spent", A1:AR90, 0))

But it doesn't work.

However If i have

=Index(A1:AR90, Match("ITXRP", C:C, 0), Match("Hours Spent", 1:1, 0))

It works just fine.

However this restricts me to always having to make sure the raw data export I get is in the format where Activity Code is in column C and the metrics headers are in Row 2.


I want the Functions to work whether I have the activity codes listed in Column M and the headers in row8, or activity codes in column E and Headers in row 4 etc.- to still work regardless of what column my activity codes are in and regardless of what row the metrics headers are in


I thought maybe a nested match like Match("IXRTP", then for array using another match that would return the column, but that match would have to have a specific row selected and you could use another nested match to get the row but then you would have to select a specific column to search.

I couldn't get it to work and I don't think that would work either because it seems to me it would run into a circular logic issue







Is there a function that will search an entire array of multiple rows and columns (A1:AC90) or an entire sheet and return a specific number for the row number and do the same for the column number such that it could be used in the index function or some other function

Thanks for any input

See attatchment

You could search on the combination of multiple columns or rows

see attatchment

use =INDEX(Data,H3,H6) to pick "hello" or

=INDEX(Data,SMALL(IF(Data=F2,ROW(Data)),1),SMALL(IF(Data=F5,COLUMN(Data)),1)) confirm with Ctrl+Shift+Enter

is it help?