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

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" wrote:

> 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

Hi Jacob,

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

Cheers
Axel


"Jacob Skaria" wrote:

> 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" wrote:
> 
> > 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