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,MA TCH(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" wrote in message
ps.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