combination IF- and VLOOKUP-function fails

A

Axel

Hello,

I've following problem:

The first table shows freight rates for different locations in relation to
specific base ports (AA and HH)!

Table 1
LOC AA - AA AA - HH HH - AA HH - HH
DKAAB 1001 1007 1013 1019
DKAAL 1002 1008 1014 1020
DKAEY 1003 1009 1015 1021
DKAZS 1004 1010 1016 1022
DKAAP 1005 1011 1017 1023
DKAGD 1006 1012 1018 1024

The second table actually shows the same data in a different format but
without the freight rates.

Table 2
From To LOC Result
HH HH DKAAB ???
HH AA DKAZS ???

Now I would like to retrieve the freight from the first table with an IF-
and VLOOPUP-function to insert the rates in the second table.

I used following formula:

=IF(AND(K13="AA",O13="AA"),VLOOKUP(Q13,'[Tariff
ZZZ.xls]HJS'!$C$2:$G$137,2,FALSE),IF(AND(K13="AA",O13="HH"),VLOOKUP(Q13,'[Tariff
ZZZ.xls]HJS'!$C$2:$G$137,3,FALSE),IF(AND(K13="HH",O13="AA"),VLOOKUP(Q13,'[Tariff
ZZZ.xls]HJS'!$C$2:$G$137,4,FALSE),IF(AND(K13="HH",O13="HH"),VLOOKUP(Q13,'[Tariff ZZZ.xls]HJS'!$C$2:$G$137,5,FALSE)))))

As the second table includes locations which are not available in the first
table there must be some #N/A results. Indeed in some cases I get these
results but my problem is that there are some cases that I get as result a
freight rate altough the location is not available in the first table!

I don't understand why this happens! Does someone have an idea what I'm
doing wrong? Or is there a smarter way how to retrieve the date?

Cheers
Axel
 
J

Jacob Skaria

Table 1 in sheet1!A:E
Table 2 in Sheet2!A:D

Try the below in Sheet2 D2 and copy down as required..

=VLOOKUP(C2,Sheet1!$A:$E,MATCH(A2&" - "&B2,Sheet1!$A$1:$E$1,0),0)

You will need to handle NA# using ISNA() if needed.
=IF(ISNA(vlookup_formula),"",vlookup_formula)
 
A

Axel

Hi Jacob,

thanks a lot for your help. Your formula works perfectly.

Cheers
Axel


Jacob Skaria said:
Table 1 in sheet1!A:E
Table 2 in Sheet2!A:D

Try the below in Sheet2 D2 and copy down as required..

=VLOOKUP(C2,Sheet1!$A:$E,MATCH(A2&" - "&B2,Sheet1!$A$1:$E$1,0),0)

You will need to handle NA# using ISNA() if needed.
=IF(ISNA(vlookup_formula),"",vlookup_formula)

--
Jacob


Axel said:
Hello,

I've following problem:

The first table shows freight rates for different locations in relation to
specific base ports (AA and HH)!

Table 1
LOC AA - AA AA - HH HH - AA HH - HH
DKAAB 1001 1007 1013 1019
DKAAL 1002 1008 1014 1020
DKAEY 1003 1009 1015 1021
DKAZS 1004 1010 1016 1022
DKAAP 1005 1011 1017 1023
DKAGD 1006 1012 1018 1024

The second table actually shows the same data in a different format but
without the freight rates.

Table 2
From To LOC Result
HH HH DKAAB ???
HH AA DKAZS ???

Now I would like to retrieve the freight from the first table with an IF-
and VLOOPUP-function to insert the rates in the second table.

I used following formula:

=IF(AND(K13="AA",O13="AA"),VLOOKUP(Q13,'[Tariff
ZZZ.xls]HJS'!$C$2:$G$137,2,FALSE),IF(AND(K13="AA",O13="HH"),VLOOKUP(Q13,'[Tariff
ZZZ.xls]HJS'!$C$2:$G$137,3,FALSE),IF(AND(K13="HH",O13="AA"),VLOOKUP(Q13,'[Tariff
ZZZ.xls]HJS'!$C$2:$G$137,4,FALSE),IF(AND(K13="HH",O13="HH"),VLOOKUP(Q13,'[Tariff ZZZ.xls]HJS'!$C$2:$G$137,5,FALSE)))))

As the second table includes locations which are not available in the first
table there must be some #N/A results. Indeed in some cases I get these
results but my problem is that there are some cases that I get as result a
freight rate altough the location is not available in the first table!

I don't understand why this happens! Does someone have an idea what I'm
doing wrong? Or is there a smarter way how to retrieve the date?

Cheers
Axel
Click to expand...
Click to expand...
 
Top