No problem: I will do my best
It is a MATCH formula nested in an INDEX formula
where INDEX(array,row,column)
- array is columns A & B on sheet 1 (your range)
- the MATCH function is used as a dynamic means of finding your row #
- the column is 2 columns over.
- think of it as coordinates when plying battleship only you do not
specifically know what row you need, so you use the match formula to find it
(if it exists).
where MATCH (reference,array,type)
- reference is the value you are looking for... in this example.. cell A1
- array is the range you want to search for the reference in this case
column A
- type is a match type: 0=Exact (what we used)... other types are defined in
your excel help
In the formula I sent you, the MATCH formula was used as the ROW in the
INDEX formula. That way, no matter what row the reference you are looking
for is in (in column A), MATCH will find it.
If there is not a match, then INDEX will return and NA#! value. The second
formula I posted takes care of that by using an IF statement and an "anti"
NA#! value function (ISNA). If you need this explained as well, let me
know. it is nice to not have NA#! show up because they can screw up SUMs
and other formulas. I basically told Excel that is the cell returns a value
of NA#!, make the cell blank (""), and if not, perform the INDEX/MATCH
formula.
Does that make sense? If not let me know.
ryanb.