Which function to use? Trying to ref a cell based on another.

  • Thread starter Thread starter scott
  • Start date Start date
S

scott

I'm starting to get confused by the vocabulary in Excel. I'm not necessarily
looking for someone to just give me a solution to my problem. I'd just like
some advice on which functions I should be reading up on. I'm still a newbie
to the formulas. If Excel would let me, the closest I could express what I'm
trying to do is something like this. =IF(A3=sheet2!A$:A$, sheet2!H$:H$

Of course this doesn't work b/c I don't know what I'm doing. I know this is
simple to some of you gurus out there, everybody starts somewhere.

To explain what I'm trying to do
A3 = a single project number
sheet2!A$:A$ = col of all project numbers on sheet2
sheet2!H$:H$ = the resulting value where A3=A$:A$ (project manager phone#)

Anyone care to point me in the right direction ..... please !

Thanks in advance for any help you can offer.
 
What you have to do is match the project number against the list of
projects, and get its row number

MATCH(A3,Sheet2!$A:$A,0)

and use this row number in a lookup into the resulting values, using INDEX

INDEX(Sheet2!$H:$H,MATCH(A3,Sheet2!$A:$A,0))

and because it may not match at all, you need to check that match returns a
number

=IF(ISNUMBER(MATCH(A3,Sheet2!$A:$A,0)),INDEX(Sheet2!$H:$H,MATCH(A3,Sheet2!$A:$A,0)),"")
 
I was way off base. I did see another post mention INDEX but the example
they used it with didn't really relate to my issue. From what your example
shows is that there is more to it. I appreciate you stepping through it
too, following your thought processes helps me understand how you pieced the
formula together.

Thank you, I think you've helped steer me in the right direction. I'm going
to read up about the functions you've used while I have my coffee this
morning.

Thank you for your help !
-Scott
 
Just trying to understand how Excel thinks when putting a formula together.
I had another question about why the MATCH function was used twice.

Did you use the MATCH function in conjunction with ISNUMBER, to check to
ensure it returns a number ?

IF(ISNUMBER(MATCH(A3,Sheet2!$A:$A,0)),INDEX(Sheet2!$H:$H,MATCH(A3,Sheet2!$A:$A,0)),"")

The way I'm reading the formula....

IF (it's a number) THEN (use the value in column H) that equals (the row
returned from the MATCH function). I'm guessing the double quotations at the
end mean use its value or something.

Is that kind of how you'd read it ?

P.S. The formula you wrote did exactly what I was trying figure out. I
just want to make sure I understand how you came up with it.

Thanks again,
scott
 
scott said:
Just trying to understand how Excel thinks when putting a formula
together.
I had another question about why the MATCH function was used twice.

Did you use the MATCH function in conjunction with ISNUMBER, to check to
ensure it returns a number ?
Click to expand...


Yes. MATCH returns the index within the lookup set if it finds the value, or
#N/A if it doesn't. ISNUMBER is a simple way to test if the MATCH was
successful or not.

IF(ISNUMBER(MATCH(A3,Sheet2!$A:$A,0)),INDEX(Sheet2!$H:$H,MATCH(A3,Sheet2!$A:$A,0)),"")

The way I'm reading the formula....

IF (it's a number) THEN (use the value in column H) that equals (the row
returned from the MATCH function). I'm guessing the double quotations at
the
end mean use its value or something.

Is that kind of how you'd read it ?
Click to expand...


More or less. If the MATCH is successful, if passes that row index to the
INDEX function to get the corresponding value in H. If it fails,the double
quotes are used to retun an empty value, so it doesn't show #N/A in the
cell.

P.S. The formula you wrote did exactly what I was trying figure out. I
just want to make sure I understand how you came up with it.
Click to expand...


No I get it, it's the only way to get better.
 
Thank for your help Mr. Phillips. I appreciate you taking the time to help
me with my problem.

Have a great weekend !
-scott
 
Back
Top