Charles Williams
12/20/2006 1:26:00 PM
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" <devill77@katamail.com> wrote in message
news:1166620223.943349.91420@t46g2000cwa.googlegroups.com...
>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" <devill77@katamail.com> wrote in message
>> > news:1166614988.470751.19080@73g2000cwn.googlegroups.com...
>> > > 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 C1:D4 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
>> > >
>