Hi guys,

I am facing a tedious problem using the VLOOKUP formula.
First of all I give to all a clear picture on what I want to do.

For ex:
A1 -> 1 B1->100 C1->2 D1->500 E1->1
A2 -> 3 B2->200 C2->4 D2->600 E2->2
A2 -> 5 B3->300 C3->6 D3->700 E3->3
A2 -> 7 B4->400 C4->8 D4->800 E4->4

I have to create a coloumn F that contains the values contained on the
coloumn B or C depending if the values on E are found in the A or C
coloumns.

So F should be:

F1->100
F2->500
F3->200
F4->600
.....

I have created this formula:

IF(ISNA(VLOOKUP(F1,$A$1:$B$4,2,FALSE)),VLOOKUP(F1,$C$1:$D$4,4,FALSE),VLOOKUP(F1,$A$1:$B$4,2,FALSE))

But in the case of F2 is not working.. It seems that even if I select
as table_array the range C14 is always checking in the coloumn A.

Anyone could help me to understand how to solve this problem?

I hope is clear.

Thanks.
BR,
Jessie

Try something like this:
=IF(isna(MATCH(E1,$A$1:$A$4,0)),INDEX($D$1:$D$4,MATCH(E1,$C$1:$C$4,0),1),INDEX($D$1:$D$4,MATCH(E1,$A$1:$A$4,0),1))

regards
Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"jessie" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi guys,
>
> I am facing a tedious problem using the VLOOKUP formula.
> First of all I give to all a clear picture on what I want to do.
>
> For ex:
> A1 -> 1 B1->100 C1->2 D1->500 E1->1
> A2 -> 3 B2->200 C2->4 D2->600 E2->2
> A2 -> 5 B3->300 C3->6 D3->700 E3->3
> A2 -> 7 B4->400 C4->8 D4->800 E4->4
>
> I have to create a coloumn F that contains the values contained on the
> coloumn B or C depending if the values on E are found in the A or C
> coloumns.
>
> So F should be:
>
> F1->100
> F2->500
> F3->200
> F4->600
> ....
>
> I have created this formula:
>
> IF(ISNA(VLOOKUP(F1,$A$1:$B$4,2,FALSE)),VLOOKUP(F1,$C$1:$D$4,4,FALSE),VLOOKUP(F1,$A$1:$B$4,2,FALSE))
>
> But in the case of F2 is not working.. It seems that even if I select
> as table_array the range C14 is always checking in the coloumn A.
>
> Anyone could help me to understand how to solve this problem?
>
> I hope is clear.
>
> Thanks.
> BR,
> Jessie
>

Charles using your formula the result of coloumn F is not the expected
one but the following:

F1->500
F2->500
F3->600
F4->600
......

What's worng?

Thanks for your help.

BR,
Jessie


Charles Williams wrote:
> Try something like this:
> =IF(isna(MATCH(E1,$A$1:$A$4,0)),INDEX($D$1:$D$4,MATCH(E1,$C$1:$C$4,0),1),INDEX($D$1:$D$4,MATCH(E1,$A$1:$A$4,0),1))
>
> regards
> Charles
> ______________________
> Decision Models
> FastExcel 2.3 now available
> Name Manager 4.0 now available
> www.DecisionModels.com
>
> "jessie" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi guys,
> >
> > I am facing a tedious problem using the VLOOKUP formula.
> > First of all I give to all a clear picture on what I want to do.
> >
> > For ex:
> > A1 -> 1 B1->100 C1->2 D1->500 E1->1
> > A2 -> 3 B2->200 C2->4 D2->600 E2->2
> > A2 -> 5 B3->300 C3->6 D3->700 E3->3
> > A2 -> 7 B4->400 C4->8 D4->800 E4->4
> >
> > I have to create a coloumn F that contains the values contained on the
> > coloumn B or C depending if the values on E are found in the A or C
> > coloumns.
> >
> > So F should be:
> >
> > F1->100
> > F2->500
> > F3->200
> > F4->600
> > ....
> >
> > I have created this formula:
> >
> > IF(ISNA(VLOOKUP(F1,$A$1:$B$4,2,FALSE)),VLOOKUP(F1,$C$1:$D$4,4,FALSE),VLOOKUP(F1,$A$1:$B$4,2,FALSE))
> >
> > But in the case of F2 is not working.. It seems that even if I select
> > as table_array the range C14 is always checking in the coloumn A.
> >
> > Anyone could help me to understand how to solve this problem?
> >
> > I hope is clear.
> >
> > Thanks.
> > BR,
> > Jessie
> >

I found the error, the correct formula is:

=IF(ISNA(MATCH(E1,$A$1:$A$4,0)),INDEX($D$1:$D$4,MATCH(E1,$C$1:$C$4,0),1),INDEX($B$1:$B$4,MATCH(E1,$A$1:$A$4,0),1))

Thanks again
BR,
Jessie

jessie wrote:
> Charles using your formula the result of coloumn F is not the expected
> one but the following:
>
> F1->500
> F2->500
> F3->600
> F4->600
> .....
>
> What's worng?
>
> Thanks for your help.
>
> BR,
> Jessie
>
>
> Charles Williams wrote:
> > Try something like this:
> > =IF(isna(MATCH(E1,$A$1:$A$4,0)),INDEX($D$1:$D$4,MATCH(E1,$C$1:$C$4,0),1),INDEX($D$1:$D$4,MATCH(E1,$A$1:$A$4,0),1))
> >
> > regards
> > Charles
> > ______________________
> > Decision Models
> > FastExcel 2.3 now available
> > Name Manager 4.0 now available
> > www.DecisionModels.com
> >
> > "jessie" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > Hi guys,
> > >
> > > I am facing a tedious problem using the VLOOKUP formula.
> > > First of all I give to all a clear picture on what I want to do.
> > >
> > > For ex:
> > > A1 -> 1 B1->100 C1->2 D1->500 E1->1
> > > A2 -> 3 B2->200 C2->4 D2->600 E2->2
> > > A2 -> 5 B3->300 C3->6 D3->700 E3->3
> > > A2 -> 7 B4->400 C4->8 D4->800 E4->4
> > >
> > > I have to create a coloumn F that contains the values contained on the
> > > coloumn B or C depending if the values on E are found in the A or C
> > > coloumns.
> > >
> > > So F should be:
> > >
> > > F1->100
> > > F2->500
> > > F3->200
> > > F4->600
> > > ....
> > >
> > > I have created this formula:
> > >
> > > IF(ISNA(VLOOKUP(F1,$A$1:$B$4,2,FALSE)),VLOOKUP(F1,$C$1:$D$4,4,FALSE),VLOOKUP(F1,$A$1:$B$4,2,FALSE))
> > >
> > > But in the case of F2 is not working.. It seems that even if I select
> > > as table_array the range C14 is always checking in the coloumn A.
> > >
> > > Anyone could help me to understand how to solve this problem?
> > >
> > > I hope is clear.
> > >
> > > Thanks.
> > > BR,
> > > Jessie
> > >

Oops, well done to fix it.

regards
Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"jessie" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I found the error, the correct formula is:
>
> =IF(ISNA(MATCH(E1,$A$1:$A$4,0)),INDEX($D$1:$D$4,MATCH(E1,$C$1:$C$4,0),1),INDEX($B$1:$B$4,MATCH(E1,$A$1:$A$4,0),1))
>
> Thanks again
> BR,
> Jessie
>
> jessie wrote:
>> Charles using your formula the result of coloumn F is not the expected
>> one but the following:
>>
>> F1->500
>> F2->500
>> F3->600
>> F4->600
>> .....
>>
>> What's worng?
>>
>> Thanks for your help.
>>
>> BR,
>> Jessie
>>
>>
>> Charles Williams wrote:
>> > Try something like this:
>> > =IF(isna(MATCH(E1,$A$1:$A$4,0)),INDEX($D$1:$D$4,MATCH(E1,$C$1:$C$4,0),1),INDEX($D$1:$D$4,MATCH(E1,$A$1:$A$4,0),1))
>> >
>> > regards
>> > Charles
>> > ______________________
>> > Decision Models
>> > FastExcel 2.3 now available
>> > Name Manager 4.0 now available
>> > www.DecisionModels.com
>> >
>> > "jessie" <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> > > Hi guys,
>> > >
>> > > I am facing a tedious problem using the VLOOKUP formula.
>> > > First of all I give to all a clear picture on what I want to do.
>> > >
>> > > For ex:
>> > > A1 -> 1 B1->100 C1->2 D1->500 E1->1
>> > > A2 -> 3 B2->200 C2->4 D2->600 E2->2
>> > > A2 -> 5 B3->300 C3->6 D3->700 E3->3
>> > > A2 -> 7 B4->400 C4->8 D4->800 E4->4
>> > >
>> > > I have to create a coloumn F that contains the values contained on
>> > > the
>> > > coloumn B or C depending if the values on E are found in the A or C
>> > > coloumns.
>> > >
>> > > So F should be:
>> > >
>> > > F1->100
>> > > F2->500
>> > > F3->200
>> > > F4->600
>> > > ....
>> > >
>> > > I have created this formula:
>> > >
>> > > IF(ISNA(VLOOKUP(F1,$A$1:$B$4,2,FALSE)),VLOOKUP(F1,$C$1:$D$4,4,FALSE),VLOOKUP(F1,$A$1:$B$4,2,FALSE))
>> > >
>> > > But in the case of F2 is not working.. It seems that even if I select
>> > > as table_array the range C14 is always checking in the coloumn A.
>> > >
>> > > Anyone could help me to understand how to solve this problem?
>> > >
>> > > I hope is clear.
>> > >
>> > > Thanks.
>> > > BR,
>> > > Jessie
>> > >
>