help on a formula please

[scott23]

Im trying to write a formula that checks to see if a certain cell
containing text, matches a range of cells that contain text.
So in column Z1:z10 i have the following: "ES", "GE", "ZB", ...
In my formula i want to see if the text in cell A20 matches anything
in the range z1:z10. If it does, then i do a vlookup of that range to
show the corresponding value of the 2nd column in the lookup.
However, if A20 is NOT in that range then i just want it to give a
value of 1.

I tried IF statements and SUM IF statements and cant get it to work.
The IF statements only see the 1st row of the range, and the sum if
statement sums up the number of rows in the range.

This is what i currently have... The first part just checks column be
to make sure there is a value<>1 in order to proceed. If B20=0 then
it just gives "".

=IF(b20<>0,IF(a20=z1:z10,VLOOKUP($a20,$z$1:$aa$10,2,FALSE),1,"")

Thanks for the help in advance.
scott

 

[Frank Kabel]

Hi Scott,

change your formula to
=IF(ISNA(VLOOKUP($A20,$Z$1:$AA$10,2,FALSE)),1,VLOOKUP($A20,$Z$1:$AA$10,
2,FALSE))

Frank

 

[Ron Rosenfeld]

Im trying to write a formula that checks to see if a certain cell
containing text, matches a range of cells that contain text.
So in column Z1:z10 i have the following: "ES", "GE", "ZB", ...
In my formula i want to see if the text in cell A20 matches anything
in the range z1:z10. If it does, then i do a vlookup of that range to
show the corresponding value of the 2nd column in the lookup.
However, if A20 is NOT in that range then i just want it to give a
value of 1.

I tried IF statements and SUM IF statements and cant get it to work.
The IF statements only see the 1st row of the range, and the sum if
statement sums up the number of rows in the range.

This is what i currently have... The first part just checks column be
to make sure there is a value<>1 in order to proceed. If B20=0 then
it just gives "".

=IF(b20<>0,IF(a20=z1:z10,VLOOKUP($a20,$z$1:$aa$10,2,FALSE),1,"")

Thanks for the help in advance.
scott

Something like:

IF(COUNTIF(Z1:Z10,A20),VLOOKUP(A20,Z1:Z10,2,FALSE),1)

(not tested)



--ron