FS: need help with Excel formulas

[daveallston]

Hello
I am desperately searching for help with how to do two things in Excel
with using formulas. I'm sure to some of the Excel experts in this
forum, they may be quite simple, but I'm killing myself searching on
the net and in Excel Help for the answers and can't figure it out. So
if you can't help me, I'll be forever greatful!!

QUESTION ONE: How do I make a formula to copy text if the value of a
cell matches another in another tab. Specifically, I am trying to
associate hockey statistics from one tab to another. I have data to be
dropped in tab "injuries". I then am trying to make a formula in my
main tab, titled "main", whereby if the name in box A6 of "main" is
equal to a name found in the range B2:B500 in "injuries", then I want
in box R6 of "main" to copy the information in box H* of "injuries"
(where the * represents the matched line from the first part of the
formula) - i.e. if the player is injured and listed in the "injuries"
tab, then when the player is found in the "main" tab, it will pull his
injury info (which is text) from H25 of "injuries" and copy the same
text to box R6.

I hope that makes sense.

QUESTION TWO: This one may be a bit simpler. And is completely separate
from the above question. I want to create a formula, whereby if H1/J1
is equal to 0 over 1 (H1 = 0 and J1 = 1), then box K1 will show the
text "DNP" (or whatever, just some kind of text). Is this possible?


Thank you SO MUCH to whoever can help me solve these two problems...
Thanks
Dave
(e-mail address removed)

 

[Ian]

Q2 In K1 put
=IF(AND(H1=0,J1=1),"DNP","")

Q1 A bit beyond me, but VLOOKUP may be worth a look.

 

[joeu2004]

QUESTION ONE: How do I make a formula to copy text if the value of a
cell matches another in another tab. Specifically, I am trying to
associate hockey statistics from one tab to another. I have data to be
dropped in tab "injuries". I then am trying to make a formula in my
main tab, titled "main", whereby if the name in box A6 of "main" is
equal to a name found in the range B2:B500 in "injuries", then I want
in box R6 of "main" to copy the information in box H* of "injuries"
(where the * represents the matched line from the first part of the
formula) - i.e. if the player is injured and listed in the "injuries"
tab, then when the player is found in the "main" tab, it will pull his
injury info (which is text) from H25 of "injuries" and copy the same
text to box R6.

I think the following does what you want. But the formula into R6:

=vlookup(A6, injuries!B2:H500, 7, false)

That searchs injuries!B2:B500 for an exact match to A6 and returns the
cell from the 7th column of the range B2:H500, which is column H.

Note that you do not really need to type injuries!B2:H500. As you type
the VLOOKUP() arguments, when you come to the second one, simply click
on the "injuries" tab and select the range B2:H500 with the mouse.

QUESTION TWO: This one may be a bit simpler. And is completely separate
from the above question. I want to create a formula, whereby if H1/J1
is equal to 0 over 1 (H1 = 0 and J1 = 1), then box K1 will show the
text "DNP" (or whatever, just some kind of text). Is this possible?

In K1, put:

=if(and(H1=0,J1=1), "DNP", "")

 

[daveallston]

Wow that is awesome. Thank you so much to you both for taking the time
to help me out.
I'm going to go try it out now, but it sounds like it will work
perfectly.
Phenomenal.
Thanks again!!!
dave

 

[daveallston]

oh I do have one other follow-up question...

for the VLOOKUP function,
when there is no match found (i.e. the cell value in cell A6 of "main"
is not found in the "injuries" tab in the range B2:H500, the result in
R6 is:

#N/A

Is there an additional part I can add to the formula in R6 to say that
if there is no match, that cell R6 remains blank?
Thanks again,
Dave

 

[joeu2004]

Is there an additional part I can add to the formula in R6 to say that
if there is no match, that cell R6 remains blank?

I figured that would come up. Unfortunately, this requires that you
write (and execute!) the vlookup twice. For example:

=if(iserror(vlookup(A6, injuries!B2:H500, 7, false)), "", vlookup(A6,
injuries!B2:H500, 7, false))

 

[Gord Dibben]

=IF(ISNA(VLOOKUP(A6,injuries!$B$2:$H$500,7,
FALSE)),"",VLOOKUP(A6,injuries!$B$2:$H$500,7, FALSE))

Note I added the absolute references for the table so it doesn't change as you
copy down.


Gord Dibben MS Excel MVP

 

[Biff]

Here's another one:

=IF(COUNTIF(injuries!$B$2:$B$500,A6),VLOOKUP(A6,injuries!$B$2:$H$500,7,0),"")

Biff