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
=Index(A1:AR90, Match(AC312, A1:AR90, 0), Match("Hours Spent", A1:AR90, 0))
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.
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