Excel 2002: Can Vlookup formula search for data with two reference
 

Dear Sir,

I need to fill up column C in List 1 by looking up references in column A
and column B at List 2.

Worksheet Illustration:

LIST 1 - Transportation Acitivity
A B C
1 FROM TO MOVE
2 KL JB 68
3 PG IP #NA
4 KL KB 10
5 KU BU 48
6 JB PG 30
7 JB SP 22
8
9
10
11 LIST 2
12 FROM TO MOVE
13 JB SP 22
14 JB PG 30
15 KL JB 68
16 SW KG 25
17 PK ML 36
18 KL KB 10
19 KU BU 48
20
21
22


May I know what formula must I input at cell C2 and copy down to get the
straight answer ?

Can VLOOKUP formula be used to search for data with two reference?

Thanks


Low


--
A36B58K641

Excel 2002: Can Vlookup formula search for data with two reference
 

Vlookup can only use one reference
work arounds include using a helper column in list 2 which would have
=A1&B1
then in a vlookup in list one use
=vlookup(A1&B1,(list2 starting with helpercolumn),2,0)

or sumproduct can be used
=sumproduct(--(list2!A$1:A$100=list1!A1),--(list2!B$1:B$100=list1!B1),list2!C$1:C$100)
(I formatted the lists as different sheets, You will need to correct the
references for you actual data)
the "--(" changes the logic true false to numeric 1,0
Also you can not use A:A as a shorthand for entire column in Sumproduct()
"Mr. Low" wrote:

Dear Sir,

I need to fill up column C in List 1 by looking up references in column A
and column B at List 2.

Worksheet Illustration:

LIST 1 - Transportation Acitivity
A B C
1 FROM TO MOVE
2 KL JB 68
3 PG IP #NA
4 KL KB 10
5 KU BU 48
6 JB PG 30
7 JB SP 22
8
9
10
11 LIST 2
12 FROM TO MOVE
13 JB SP 22
14 JB PG 30
15 KL JB 68
16 SW KG 25
17 PK ML 36
18 KL KB 10
19 KU BU 48
20
21
22


May I know what formula must I input at cell C2 and copy down to get the
straight answer ?

Can VLOOKUP formula be used to search for data with two reference?

Thanks


Low


--
A36B58K641

Excel 2002: Can Vlookup formula search for data with two reference
 

Using your illustration:

=INDEX($C$13:$C$19,MATCH(1,(A2=$A$13:$A$19)*(B2=$B $13:$B$19),0))

Entered with Ctrl+Shift+Enter (an array formula)

HTH

"Mr. Low" wrote:

Dear Sir,

I need to fill up column C in List 1 by looking up references in column A
and column B at List 2.

Worksheet Illustration:

LIST 1 - Transportation Acitivity
A B C
1 FROM TO MOVE
2 KL JB 68
3 PG IP #NA
4 KL KB 10
5 KU BU 48
6 JB PG 30
7 JB SP 22
8
9
10
11 LIST 2
12 FROM TO MOVE
13 JB SP 22
14 JB PG 30
15 KL JB 68
16 SW KG 25
17 PK ML 36
18 KL KB 10
19 KU BU 48
20
21
22


May I know what formula must I input at cell C2 and copy down to get the
straight answer ?

Can VLOOKUP formula be used to search for data with two reference?

Thanks


Low


--
A36B58K641

Excel 2002: Can Vlookup formula search for data with two refer
 

Hello BJ,

I modified the formula to =VLOOKUP(A2&B2,(A$15&A$15:B$25&B$25),2,0)
as my data starts at A2&B2.

However I got #VALUE! .

May I know what should be the correct formula ?

Thanks

Low


--
A36B58K641


"bj" wrote:

Vlookup can only use one reference
work arounds include using a helper column in list 2 which would have
=A1&B1
then in a vlookup in list one use
=vlookup(A1&B1,(list2 starting with helpercolumn),2,0)

or sumproduct can be used
=sumproduct(--(list2!A$1:A$100=list1!A1),--(list2!B$1:B$100=list1!B1),list2!C$1:C$100)
(I formatted the lists as different sheets, You will need to correct the
references for you actual data)
the "--(" changes the logic true false to numeric 1,0
Also you can not use A:A as a shorthand for entire column in Sumproduct()
"Mr. Low" wrote:

Dear Sir,

I need to fill up column C in List 1 by looking up references in column A
and column B at List 2.

Worksheet Illustration:

LIST 1 - Transportation Acitivity
A B C
1 FROM TO MOVE
2 KL JB 68
3 PG IP #NA
4 KL KB 10
5 KU BU 48
6 JB PG 30
7 JB SP 22
8
9
10
11 LIST 2
12 FROM TO MOVE
13 JB SP 22
14 JB PG 30
15 KL JB 68
16 SW KG 25
17 PK ML 36
18 KL KB 10
19 KU BU 48
20
21
22


May I know what formula must I input at cell C2 and copy down to get the
straight answer ?

Can VLOOKUP formula be used to search for data with two reference?

Thanks


Low


--
A36B58K641

Excel 2002: Can Vlookup formula search for data with two refer
 

Hello Toppers,

Thanks for your formula.

It works very well.

Kind Regards

Low

--
A36B58K641


"Toppers" wrote:

Using your illustration:

=INDEX($C$13:$C$19,MATCH(1,(A2=$A$13:$A$19)*(B2=$B $13:$B$19),0))

Entered with Ctrl+Shift+Enter (an array formula)

HTH

"Mr. Low" wrote:

Dear Sir,

I need to fill up column C in List 1 by looking up references in column A
and column B at List 2.

Worksheet Illustration:

LIST 1 - Transportation Acitivity
A B C
1 FROM TO MOVE
2 KL JB 68
3 PG IP #NA
4 KL KB 10
5 KU BU 48
6 JB PG 30
7 JB SP 22
8
9
10
11 LIST 2
12 FROM TO MOVE
13 JB SP 22
14 JB PG 30
15 KL JB 68
16 SW KG 25
17 PK ML 36
18 KL KB 10
19 KU BU 48
20
21
22


May I know what formula must I input at cell C2 and copy down to get the
straight answer ?

Can VLOOKUP formula be used to search for data with two reference?

Thanks


Low


--
A36B58K641

Excel 2002: Can Vlookup formula search for data with two refer
 

enter a new column C
in C15 enter =A15&B15
copy down to C25
in C2 enter
=vlookup(A2&B2,C15:D15,2,0)

"Mr. Low" wrote:

Hello BJ,

I modified the formula to =VLOOKUP(A2&B2,(A$15&A$15:B$25&B$25),2,0)
as my data starts at A2&B2.

However I got #VALUE! .

May I know what should be the correct formula ?

Thanks

Low


--
A36B58K641


"bj" wrote:

Vlookup can only use one reference
work arounds include using a helper column in list 2 which would have
=A1&B1
then in a vlookup in list one use
=vlookup(A1&B1,(list2 starting with helpercolumn),2,0)

or sumproduct can be used
=sumproduct(--(list2!A$1:A$100=list1!A1),--(list2!B$1:B$100=list1!B1),list2!C$1:C$100)
(I formatted the lists as different sheets, You will need to correct the
references for you actual data)
the "--(" changes the logic true false to numeric 1,0
Also you can not use A:A as a shorthand for entire column in Sumproduct()
"Mr. Low" wrote:

Dear Sir,

I need to fill up column C in List 1 by looking up references in column A
and column B at List 2.

Worksheet Illustration:

LIST 1 - Transportation Acitivity
A B C
1 FROM TO MOVE
2 KL JB 68
3 PG IP #NA
4 KL KB 10
5 KU BU 48
6 JB PG 30
7 JB SP 22
8
9
10
11 LIST 2
12 FROM TO MOVE
13 JB SP 22
14 JB PG 30
15 KL JB 68
16 SW KG 25
17 PK ML 36
18 KL KB 10
19 KU BU 48
20
21
22


May I know what formula must I input at cell C2 and copy down to get the
straight answer ?

Can VLOOKUP formula be used to search for data with two reference?

Thanks


Low


--
A36B58K641

Excel 2002: Can Vlookup formula search for data with two refer
 

Hello BJ,

Yes, your formula works well now.

Many thanks.

Low

--
A36B58K641


"bj" wrote:

enter a new column C
in C15 enter =A15&B15
copy down to C25
in C2 enter
=vlookup(A2&B2,C15:D15,2,0)

"Mr. Low" wrote:

Hello BJ,

I modified the formula to =VLOOKUP(A2&B2,(A$15&A$15:B$25&B$25),2,0)
as my data starts at A2&B2.

However I got #VALUE! .

May I know what should be the correct formula ?

Thanks

Low


--
A36B58K641


"bj" wrote:

Vlookup can only use one reference
work arounds include using a helper column in list 2 which would have
=A1&B1
then in a vlookup in list one use
=vlookup(A1&B1,(list2 starting with helpercolumn),2,0)

or sumproduct can be used
=sumproduct(--(list2!A$1:A$100=list1!A1),--(list2!B$1:B$100=list1!B1),list2!C$1:C$100)
(I formatted the lists as different sheets, You will need to correct the
references for you actual data)
the "--(" changes the logic true false to numeric 1,0
Also you can not use A:A as a shorthand for entire column in Sumproduct()
"Mr. Low" wrote:

Dear Sir,

I need to fill up column C in List 1 by looking up references in column A
and column B at List 2.

Worksheet Illustration:

LIST 1 - Transportation Acitivity
A B C
1 FROM TO MOVE
2 KL JB 68
3 PG IP #NA
4 KL KB 10
5 KU BU 48
6 JB PG 30
7 JB SP 22
8
9
10
11 LIST 2
12 FROM TO MOVE
13 JB SP 22
14 JB PG 30
15 KL JB 68
16 SW KG 25
17 PK ML 36
18 KL KB 10
19 KU BU 48
20
21
22


May I know what formula must I input at cell C2 and copy down to get the
straight answer ?

Can VLOOKUP formula be used to search for data with two reference?

Thanks


Low


--
A36B58K641