HELP!!! VLOOKUP Formula Help

[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',''))))))))

[Franz Verga]

Nel post news:59617A35-5EB4-478E-BF18-62DC407949E1@microsoft.com
*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',''))))))))


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

[Franz Verga]

Nel post news:u0IC7btlGHA.2180@TK2MSFTNGP05.phx.gbl
*Franz Verga* ha scritto:


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

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

[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.

"wnfisba" wrote:

> 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',''))))))))

[Toppers]

As Franz, numbers are without quotes (another dumb moment!)

"Toppers" wrote:

> 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.
>
> "wnfisba" wrote:
>
> > 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',''))))))))

[Franz Verga]

Nel post news:27B85112-0245-49BA-9CE3-ED090CD30D75@microsoft.com
*Toppers* ha scritto:

> As Franz, numbers are without quotes (another dumb moment!)
>
Don't worry... I forgot to write about the double quote around X... It
happens... :-)

--
Ciao

Franz Verga from Italy

[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!





"Toppers" wrote:

> 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.
>
> "wnfisba" wrote:
>
> > 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',''))))))))

[Franz Verga]

Nel post news:F57314A2-BF89-4A54-BAFD-D18E8E54D9CA@microsoft.com
*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",""))))))),"")
>

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

[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,""))))))),"")

"Franz Verga" wrote:

> Nel post news:F57314A2-BF89-4A54-BAFD-D18E8E54D9CA@microsoft.com
> *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",""))))))),"")
> >
>
> 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
>
>
>

[Franz Verga]

Nel post news:9AED98CC-2C84-483A-8946-B4D4D9031121@microsoft.com
*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,""))))))),"")
>
> "Franz Verga" wrote:
>
>> Nel post news:F57314A2-BF89-4A54-BAFD-D18E8E54D9CA@microsoft.com
>> *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",""))))))),"")
>>>
>>
>> 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

--
(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

[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

"wnfisba" wrote:

> 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',''))))))))

[wnfisba]

You read my mind Simon!

That's exactly what I did!

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

"SimonCC" wrote:

> 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
>
> "wnfisba" wrote:
>
> > 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',''))))))))