Match Function

[Guest]

We are trying to use the match function to locate a value in a table of data. The basic spreadsheet set up is the top row contains years (1,2,...18) the main column contains titles (Nurse, Doctor, etc.) the middle data contains salary information for each year. We are trying to find a year based on someone's title and their current salary. We can locate what row the title is falling on with the Match function. The only problem we are having is that we need to use that variable row information to determine where the next match function will look for the appropriate salary information. Does anyone know how to use a variable lookup_array to find data using the Match function?

In testing our function, it works if we manually enter the row we want it to look in, so we have the right formula set up, it is just trying to determine how to use this formula when you need the row to change with each Title that you are working with.

Thanks in advance for any and all assistance

 

[Bernie Deitrick]

Johna and Karen,

If your data starts in cell A1, row 1 has years, and column A has job
titles, then you can use this formula

=OFFSET(A1,0,MATCH(100000,OFFSET(A1,MATCH("Doctor",A:A,FALSE)-1,0,1,20),FALS
E)-1)

The 100000 (the salary of interest) can be a cell reference, and if you
don't require an exact match, then change the second instance of FALSE to
TRUE. (so you could enter 100000 and find the year that a doctor made less
than or equal to that amount, say 99000 but not 105000.)
The "Doctor" part can also be a cell reference, and it should always require
an exact macth, so leave the first instance of FALSE alone.

HTH,
Bernie
MS Excel MVP

We are trying to use the match function to locate a value in a table of

data. The basic spreadsheet set up is the top row contains years
(1,2,...18) the main column contains titles (Nurse, Doctor, etc.) the middle
data contains salary information for each year. We are trying to find a
year based on someone's title and their current salary. We can locate what
row the title is falling on with the Match function. The only problem we
are having is that we need to use that variable row information to determine
where the next match function will look for the appropriate salary
information. Does anyone know how to use a variable lookup_array to find
data using the Match function?

In testing our function, it works if we manually enter the row we want it

to look in, so we have the right formula set up, it is just trying to
determine how to use this formula when you need the row to change with each
Title that you are working with.

 

[Marcus Warham]

We are trying to use the match function to locate a value in a table of

data. The basic spreadsheet set up is the top row contains years
(1,2,...18) the main column contains titles

Can I suggest trying a regexp?


(Nurse, Doctor, etc.) the middle data contains salary information for each
year. We are trying to find a year based on someone's title and their
current salary. We can locate what row the title is falling on with the
Match function. The only problem we are having is that we need to use that
variable row information to determine where the next match function will
look for the appropriate salary information. Does anyone know how to use a
variable lookup_array to find data using the Match function?

In testing our function, it works if we manually enter the row we want it

to look in, so we have the right formula set up, it is just trying to
determine how to use this formula when you need the row to change with each
Title that you are working with.