Big Problem using VLOOKUP formula

[jessie]

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 Williams]

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]

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

 

[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

 

[Charles Williams]

Oops, well done to fix it.

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