VLOOKUP Value in Another Formula

  • Thread starter Thread starter Jerry
  • Start date Start date
J

Jerry

Help, I've almost given up on this.

I'm using VLOOKUP in a certain cell. This formula returns a value from
another worksheet. I have this part down pat.

What I want to do is use the value returned from the cell having the
VLOOKUP formula in another cell so I can label the other cell a certain
way. My VLOOKUP formula is:

=IF(ISNA(VLOOKUP($G$18,'DJC2 ARN DATA'!$A$5:$U$3005,8,FALSE))," ",VLOOKUP
($G$18,'DJC2 ARN DATA'!$A$5:$U$3005,8,FALSE))

Let's say that this VLOOKUP formula is in cell G21.

I want to put "PRIMARY NUMBER" in cell G16 if the value retuned in G21 is
1, 2, or 3. In other words, I want to label G16 as "PRIMARY NUMBER" if the
value returned by the VLOOKUP formula in G21 returns a 1, 2, or 3.

I suspect I have to use an IF statement to do this, but I'm struggling to
get it to work.

I will certainly appreciate any help I can get.

TIA,
Jerry
 
=if(or(g21=1,g21=2,g21=3),"primary number","not primary number")
or
=if(or(g21={1,2,3}),"primary number","not primary number")

ps. I wouldn't use " " in that =vlookup() formula. I'd use "".
 
Dave,

I tried your formula. It appears that it evaluates the text string of the
VLOOKUP formula instead of the returned value. Any suggestions?
 
I'm not sure I understand.

Are you saying that the formula I suggested is looking at the formula in G21
instead of the value that appears in G21?

That's not how formulas work in excel.

But maybe your =vlookup() formula isn't returning the numbers 1, 2, 3. It could
be returning text values (not numeric) "1", "2", "3" (w/o the quotes, though).
Another problem could be that your formula isn't returning a whole number--maybe
the value is something like 2.999999999999 or 1.000000000001.

Maybe you could try these formulas in a few empty cells to check to see what's
happening in G21:

=isnumber(g21)
=istext(g21)
=len(g21)

If =isnumber() returns False, then you could change the formula to:
=if(or(g21={"1","2","3"}),"primary number","not primary number")

If =len() returns something besides 1, then maybe you have spaces (or other
whitespace) in that returned value.

Instead of guessing more, I'll wait for your reply.
 
Dave, My ignorance has manifested itself again. You are absolutely
correct. The value being returned was text vice numeric. I applied your
recomendation and it worked fine.

I do appreciate all of your help with this.
 
Without knowing what you're really doing (it's never stopped me in the past),
you may want to consider changing the source data from text to real
numbers--especially if it's something that should be used in calculations in
other formulas.
 
Back
Top