I am trying to accomplish a vlookup within the IF statement but it won't work


=IF(VLOOKUP(A2,RPT7571!B:I,4,FALSE)="2",VLOOKUP(A2 ,RPT7571!B:I,5,FALSE),"")

I have 2 spreadsheets with info. I am trying to say in a cell in
spreadsheet 1 - look into speadheet RPT7571 and if A2 matches A2 in my
spreadsheet 1 then look over 4 columns and if the 4th column has a value of 2
then return the value in column 5 of the RPT7571 spreadsheet into my
spreadsheet 1 otherqise return nothing.

It does not work
Help

--
Thanks
ETC

Your formula states that if you search for the value of A2 in RPT7571's
Column B, not Column A, finds it, and looks 4 cols across and finds a 2, it
must retrieve the value five Cols across. As such it does not check A2 in
RPT7571, but checks the entire column B:I in said book. If it finds the
value of A2 in any cell (row) in Col B of said book, it looks 4 columns
across (Col E) whether it contains a 2. If it does, it retrieves the value
in col F of that row, not of row 2. It also does not matter whether A2 is
the same in the two books, because you are looking for the value in A2 in the
one book in Col B of the other book. If you can more clearly define what it
is you want to achieve, the answer will become clear!

Do you want to search Col B in RPT7571 for the value in A2, or do you want
to compare the value of the two A2's.

"Sweetetc" wrote:

I am trying to accomplish a vlookup within the IF statement but it won't work


=IF(VLOOKUP(A2,RPT7571!B:I,4,FALSE)="2",VLOOKUP(A2 ,RPT7571!B:I,5,FALSE),"")

I have 2 spreadsheets with info. I am trying to say in a cell in
spreadsheet 1 - look into speadheet RPT7571 and if A2 matches A2 in my
spreadsheet 1 then look over 4 columns and if the 4th column has a value of 2
then return the value in column 5 of the RPT7571 spreadsheet into my
spreadsheet 1 otherqise return nothing.

It does not work
Help

--
Thanks
ETC

Your logic seems sound, but maybe test for a number

=IF(VLOOKUP(A2,RPT7571!B:I,4,FALSE)=2,VLOOKUP(A2,R PT7571!B:I,5,FALSE),"")

Will A2 always be found, if not you may want to test it

=IF(ISNA(VLOOKUP(A2,RPT7571!B:I,4,FALSE)),IF(VLOOK UP(A2,RPT7571!B:I,4,FALSE)=2,VLOOKUP(A2,RPT7571!B: I,5,FALSE),""))



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Sweetetc" wrote in message
...
I am trying to accomplish a vlookup within the IF statement but it won't
work


=IF(VLOOKUP(A2,RPT7571!B:I,4,FALSE)="2",VLOOKUP(A2 ,RPT7571!B:I,5,FALSE),"")

I have 2 spreadsheets with info. I am trying to say in a cell in
spreadsheet 1 - look into speadheet RPT7571 and if A2 matches A2 in my
spreadsheet 1 then look over 4 columns and if the 4th column has a value
of 2
then return the value in column 5 of the RPT7571 spreadsheet into my
spreadsheet 1 otherqise return nothing.

It does not work
Help

--
Thanks
ETC

Kassie

Thanks I misspoke trying to oversimplify the wording of my example. You are
correct I am trying to match the value in spreadsheet A2 to the values in B
on the TPT7541 spreadsheet and if the corresponding row 4 columns over (E)
equals "2" the return the value of the corresponding column 5 (F)

Hope this helps as it does not seem to work. I have validated that there is
a match and a value should be returned into spreadsheet 1.

Thanks
--
Thanks
ETC


"kassie" wrote:

Your formula states that if you search for the value of A2 in RPT7571's
Column B, not Column A, finds it, and looks 4 cols across and finds a 2, it
must retrieve the value five Cols across. As such it does not check A2 in
RPT7571, but checks the entire column B:I in said book. If it finds the
value of A2 in any cell (row) in Col B of said book, it looks 4 columns
across (Col E) whether it contains a 2. If it does, it retrieves the value
in col F of that row, not of row 2. It also does not matter whether A2 is
the same in the two books, because you are looking for the value in A2 in the
one book in Col B of the other book. If you can more clearly define what it
is you want to achieve, the answer will become clear!

Do you want to search Col B in RPT7571 for the value in A2, or do you want
to compare the value of the two A2's.

"Sweetetc" wrote:

I am trying to accomplish a vlookup within the IF statement but it won't work


=IF(VLOOKUP(A2,RPT7571!B:I,4,FALSE)="2",VLOOKUP(A2 ,RPT7571!B:I,5,FALSE),"")

I have 2 spreadsheets with info. I am trying to say in a cell in
spreadsheet 1 - look into speadheet RPT7571 and if A2 matches A2 in my
spreadsheet 1 then look over 4 columns and if the 4th column has a value of 2
then return the value in column 5 of the RPT7571 spreadsheet into my
spreadsheet 1 otherqise return nothing.

It does not work
Help

--
Thanks
ETC

Bob

When I enter this formula I get a return of FALSE yet I now there is a value
that should be entered. AS i can visually see the match.
--
Thanks
ETC


"Bob Phillips" wrote:

Your logic seems sound, but maybe test for a number

=IF(VLOOKUP(A2,RPT7571!B:I,4,FALSE)=2,VLOOKUP(A2,R PT7571!B:I,5,FALSE),"")

Will A2 always be found, if not you may want to test it

=IF(ISNA(VLOOKUP(A2,RPT7571!B:I,4,FALSE)),IF(VLOOK UP(A2,RPT7571!B:I,4,FALSE)=2,VLOOKUP(A2,RPT7571!B: I,5,FALSE),""))



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Sweetetc" wrote in message
...
I am trying to accomplish a vlookup within the IF statement but it won't
work


=IF(VLOOKUP(A2,RPT7571!B:I,4,FALSE)="2",VLOOKUP(A2 ,RPT7571!B:I,5,FALSE),"")

I have 2 spreadsheets with info. I am trying to say in a cell in
spreadsheet 1 - look into speadheet RPT7571 and if A2 matches A2 in my
spreadsheet 1 then look over 4 columns and if the 4th column has a value
of 2
then return the value in column 5 of the RPT7571 spreadsheet into my
spreadsheet 1 otherqise return nothing.

It does not work
Help

--
Thanks
ETC

if you can visulally see what looks like a match
if the data is text
try
=a2=RPT7571!B4
(if B4 is the apparent match)
if the answer is false check
=len(a2)

and
=len(RPT7571!B4)

you may need to put a trim() function in your equation

if the data is numeric try increasing the number of significant figures to
insure that it is not a round off issue in both cells.
also one may truely be text even if it looks numeric

"Sweetetc" wrote:

I am trying to accomplish a vlookup within the IF statement but it won't work


=IF(VLOOKUP(A2,RPT7571!B:I,4,FALSE)="2",VLOOKUP(A2 ,RPT7571!B:I,5,FALSE),"")

I have 2 spreadsheets with info. I am trying to say in a cell in
spreadsheet 1 - look into speadheet RPT7571 and if A2 matches A2 in my
spreadsheet 1 then look over 4 columns and if the 4th column has a value of 2
then return the value in column 5 of the RPT7571 spreadsheet into my
spreadsheet 1 otherqise return nothing.

It does not work
Help

--
Thanks
ETC

BJ

Thanks it is not a length issue though
Thanks
ETC


"bj" wrote:

if you can visulally see what looks like a match
if the data is text
try
=a2=RPT7571!B4
(if B4 is the apparent match)
if the answer is false check
=len(a2)

and
=len(RPT7571!B4)

you may need to put a trim() function in your equation

if the data is numeric try increasing the number of significant figures to
insure that it is not a round off issue in both cells.
also one may truely be text even if it looks numeric

"Sweetetc" wrote:

I am trying to accomplish a vlookup within the IF statement but it won't work


=IF(VLOOKUP(A2,RPT7571!B:I,4,FALSE)="2",VLOOKUP(A2 ,RPT7571!B:I,5,FALSE),"")

I have 2 spreadsheets with info. I am trying to say in a cell in
spreadsheet 1 - look into speadheet RPT7571 and if A2 matches A2 in my
spreadsheet 1 then look over 4 columns and if the 4th column has a value of 2
then return the value in column 5 of the RPT7571 spreadsheet into my
spreadsheet 1 otherqise return nothing.

It does not work
Help

--
Thanks
ETC

NOTE for anyone trying to accomplish the same issue . The formula is
correct. The only adjustment I had to do was to change "2" to 2 since I was
looking at a number and not text.
--
Thanks
ETC


"Sweetetc" wrote:

Kassie

Thanks I misspoke trying to oversimplify the wording of my example. You are
correct I am trying to match the value in spreadsheet A2 to the values in B
on the TPT7541 spreadsheet and if the corresponding row 4 columns over (E)
equals "2" the return the value of the corresponding column 5 (F)

Hope this helps as it does not seem to work. I have validated that there is
a match and a value should be returned into spreadsheet 1.

Thanks
--
Thanks
ETC


"kassie" wrote:

Your formula states that if you search for the value of A2 in RPT7571's
Column B, not Column A, finds it, and looks 4 cols across and finds a 2, it
must retrieve the value five Cols across. As such it does not check A2 in
RPT7571, but checks the entire column B:I in said book. If it finds the
value of A2 in any cell (row) in Col B of said book, it looks 4 columns
across (Col E) whether it contains a 2. If it does, it retrieves the value
in col F of that row, not of row 2. It also does not matter whether A2 is
the same in the two books, because you are looking for the value in A2 in the
one book in Col B of the other book. If you can more clearly define what it
is you want to achieve, the answer will become clear!

Do you want to search Col B in RPT7571 for the value in A2, or do you want
to compare the value of the two A2's.

"Sweetetc" wrote:

I am trying to accomplish a vlookup within the IF statement but it won't work


=IF(VLOOKUP(A2,RPT7571!B:I,4,FALSE)="2",VLOOKUP(A2 ,RPT7571!B:I,5,FALSE),"")

I have 2 spreadsheets with info. I am trying to say in a cell in
spreadsheet 1 - look into speadheet RPT7571 and if A2 matches A2 in my
spreadsheet 1 then look over 4 columns and if the 4th column has a value of 2
then return the value in column 5 of the RPT7571 spreadsheet into my
spreadsheet 1 otherqise return nothing.

It does not work
Help

--
Thanks
ETC