HELP!!! VLOOKUP Formula Help

W

wnfisba

Can anyone tell me what's wrong with this formula???

It doesn't seem to like the
(IF(Sheet2!$Z2='X','1',
portion of the formula and is highlighting the 'X'...

PLEASE HELP! I'm desperate...

Here's the whole formula...

=IF(VLOOKUP(A2,Sheet2!$B$2:$B$19634,(IF(Sheet2!$Z2='X','1',IF(Sheet2!$AA2='X','4',IF(Sheet2!$AB2='X','2',IF(Sheet2!$AC2='X','5',IF(Sheet2!$AD2='X','3',''))))))))
 
F

Franz Verga

Nel post *wnfisba* ha scritto:
Can anyone tell me what's wrong with this formula???

It doesn't seem to like the
(IF(Sheet2!$Z2='X','1',
portion of the formula and is highlighting the 'X'...

PLEASE HELP! I'm desperate...

Here's the whole formula...

=IF(VLOOKUP(A2,Sheet2!$B$2:$B$19634,(IF(Sheet2!$Z2='X','1',IF(Sheet2!$AA2='X','4',IF(Sheet2!$AB2='X','2',IF(Sheet2!$AC2='X','5',IF(Sheet2!$AD2='X','3',''))))))))
Click to expand...


Try in this way:

=IF(VLOOKUP(A2,Sheet2!$B$2:$B$19634,(IF(Sheet2!$Z2='X',1,IF(Sheet2!$AA2='X',4,IF(Sheet2!$AB2='X',2,IF(Sheet2!$AC2='X',5,IF(Sheet2!$AD2='X',3,''))))))))

You don't single quote around column's number...


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
F

Franz Verga

Nel post *Franz Verga* ha scritto:

You don't single quote around column's number...
Click to expand...

should be intended as

You don't need single quote or quote around column's number...


--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
T

Toppers

Your single quotes around X should be double quotes "" and the same for the
numeric values e.g. '1' should be "1".

The formula appers incomplete as you don't action the true/false conditions
on the major IF. And probably should the FALSE parameter in the VLOOKUP
statement. And VLOOKUP only has one column in the range .....

Perhaps you could explain what you are trying to do.
 
F

Franz Verga

Nel post *Toppers* ha scritto:
As Franz, numbers are without quotes (another dumb moment!)
Click to expand...
Don't worry... I forgot to write about the double quote around X... It
happens... :)
 
W

wnfisba

Ok...Fixed the single quote problem. I actually saw that before I had a
chance to come back here. Here's what I'm trying to do. I'm trying to look up
file ids in Sheet 2 with what's in Sheet 1. When I find the file id in Sheet
2, I then want to evaluate a Race column on sheet 2. If the race column on
sheet 2 is valued with an "X", then I want to return a value to the Race
column on sheet 1.

Right now, the formula returns a #VALUE!

Here's the formula...Any help would be GREATLY appreciated...

=IF(VLOOKUP(B1069,Sheet2!$C$2:$C$19634,(IF(Sheet2!$AA$2="X","1",IF(Sheet2!$AB$2="X","4",IF(Sheet2!$AC$2="X","2",IF(Sheet2!$AD$2="X","5",IF(Sheet2!$AE$2="X","3",""))))))),"")

Thanks!
 
F

Franz Verga

Nel post *wnfisba* ha scritto:
Ok...Fixed the single quote problem. I actually saw that before I had
a chance to come back here. Here's what I'm trying to do. I'm trying
to look up file ids in Sheet 2 with what's in Sheet 1. When I find
the file id in Sheet 2, I then want to evaluate a Race column on
sheet 2. If the race column on sheet 2 is valued with an "X", then I
want to return a value to the Race column on sheet 1.

Right now, the formula returns a #VALUE!

Here's the formula...Any help would be GREATLY appreciated...

=IF(VLOOKUP(B1069,Sheet2!$C$2:$C$19634,(IF(Sheet2!$AA$2="X","1",IF(Sheet2!$AB$2="X","4",IF(Sheet2!$AC$2="X","2",IF(Sheet2!$AD$2="X","5",IF(Sheet2!$AE$2="X","3",""))))))),"")
Click to expand...

You *don't* need any quote around the numbers. The X's are Ok, but numbers
*don't* need quote.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
W

wnfisba

That wasn't it. I removed the quotes around the numbers and still got a
#VALUE!. I do want to return those values, 1,2,3,4,etc.., if an "X" is found
in Sheet 2.

Here's the formula up-to-date..

=IF(VLOOKUP(B1069,Sheet2!$C$2:$C$19634,(IF(Sheet2!$AA$2="X",1,IF(Sheet2!$AB$2="X",4,IF(Sheet2!$AC$2="X",2,IF(Sheet2!$AD$2="X",5,IF(Sheet2!$AE$2="X",3,""))))))),"")
 
F

Franz Verga

Nel post *wnfisba* ha scritto:

The numbers are returned from the IF's to VLOOKUP as column numbers in which
there are the data you want.
But maybe I don't understand what are you tying to do...
That wasn't it. I removed the quotes around the numbers and still got
a #VALUE!. I do want to return those values, 1,2,3,4,etc.., if an "X"
is found in Sheet 2.

Here's the formula up-to-date..

=IF(VLOOKUP(B1069,Sheet2!$C$2:$C$19634,(IF(Sheet2!$AA$2="X",1,IF(Sheet2!$AB$2="X",4,IF(Sheet2!$AC$2="X",2,IF(Sheet2!$AD$2="X",5,IF(Sheet2!$AE$2="X",3,""))))))),"")
Click to expand...

--
(I'm not sure of names of menues, option and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
S

SimonCC

After looking through all of the posts, I think I understand what you're
trying to do. You shoud probably do this in two steps.

First, use an evaluation column (column D if currently not used) in Sheet2
to show values of 1, 2, 3, 4, 5:
=IF(Sheet2!AA2="X",1,IF(Sheet2!AB2="X",4,IF(Sheet2!AC2="X",2,IF(Sheet2!AD2="X",5,IF(Sheet2!$AE$2="X",3,"")))))

Then in Sheet1:
=IF(ISNA(VLOOKUP(B1069,Sheet2!$C$2:$D$19634,2,FALSE)),"",VLOOKUP(B1069,Sheet2!$C$2:$D$19634,2,FALSE))

If you end up using a different column in Sheet2 as evaluation column, your
VLOOKUP range and return column would need to be adjusted as well.

-Simon
 
W

wnfisba

You read my mind Simon!

That's exactly what I did!

Sometimes I forget KISS.(Keep It Simple Stupid!)
 
Top