# VLOOKUP with two columns' "lookup_value"

This is a discussion on VLOOKUP with two columns' "lookup_value" within the Excel Questions forums, part of the Question Forums category; How can I use some formula like vlookup to vlookup two columns' "lookup_value" from two workbooks (test_a.xls; test_b.xls)? both sheets ...

1. How can I use some formula like vlookup to vlookup two columns' "lookup_value" from two workbooks (test_a.xls; test_b.xls)?

both sheets have the same columns as:
column A = Part Number
column B = Ref Number
column C = Price

but in test_b.xls, price's value is empty, have to be extracted from test_a.xls if both columns A&B are matched.

tks.
CL

2. Something like this:

IF(VLOOKUP(A5,A1:C3,3,FALSE),VLOOKUP(A5,A1:C3,3,FALSE),VLOOKUP(A5,Sheet2!A1:C3,3,FALSE))

You will need to adjust the range and sheet references to suit.

3. I think you need to create a fourth column in both tables and make it the first column and it will have a formual concatinating both the Partnumber and the refnumber
=text(B2)&"-"&text(C2)
then you can use this column as the key in your lookup

4. On 2002-08-01 01:40, Andrew Poulsom wrote:
Something like this:

IF(VLOOKUP(A5,A1:C3,3,FALSE),VLOOKUP(A5,A1:C3,3,FALSE),VLOOKUP(A5,Sheet2!A1:C3,3,FALSE))

You will need to adjust the range and sheet references to suit.
thanks for your suggestion, but it seem don't work.
besides, here are some extra info:
-both workbook have only one sheet, sheet1

tks.
CL

5. Sorry I missed the 2 books bit:

IF(VLOOKUP(A5,A1:C3,3,FALSE),VLOOKUP(A5,A1:C3,3,FALSE),VLOOKUP(A5,[test_b.xls]Sheet1!A1:C3,3,FALSE))

test_b.xls must be open when you type this. You may need to change the sheet reference.

6. tks.
it work.

7. On 2002-08-01 02:30, Andrew Poulsom wrote:
Sorry I missed the 2 books bit:

IF(VLOOKUP(A5,A1:C3,3,FALSE),VLOOKUP(A5,A1:C3,3,FALSE),VLOOKUP(A5,[test_b.xls]Sheet1!A1:C3,3,FALSE))

test_b.xls must be open when you type this. You may need to change the sheet reference.
sorry, how about to compare two separeate columns , ie. column A & C, not two consecutive columns, A & B, and the price is in column D of test_b.xls.

tks.
CL

8. On 2002-08-01 01:24, clwong wrote:
How can I use some formula like vlookup to vlookup two columns' "lookup_value" from two workbooks (test_a.xls; test_b.xls)?

both sheets have the same columns as:
column A = Part Number
column B = Ref Number
column C = Price

but in test_b.xls, price's value is empty, have to be extracted from test_a.xls if both columns A&B are matched.

tks.
CL
It's unclear where you want the lookup formula -- in a third book?

And it's also a bit vague whether a Part Number is unique. If not, is the combination of Part Number and Ref number unique?

9. You should be able to adapt my formula to look up what you want.

If the prices are in column D change:

A1:C3,3

to

A1:D3,4

10. Sorry for late reply.

******** ******************** ************************************************************************>
 Microsoft Excel - Test_B.xls ___Running: xl97 : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 A1 =

A
B
C
D
1
P/NRefR*P/N*2Price
2
222qqqq222A*
3
222*222AB*
4
233jjj233A*
5
822rrrr822A*
6
822*822AB*
7
822*822AC*
8
833yyyy833A*
9
855pppp855A*
 Sheet1 *

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Source Sheet:
******** ******************** ************************************************************************>
 Microsoft Excel - Test_A.xls ___Running: xl97 : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 A1 =

A
B
C
D
1
P/NRefR*P/N*Price
2
222ggggg222A6
3
222kkk222AB8.5
4
233hhhh233A7
5
822ddd822A4
6
822ii822AB5.5
7
822iid822AC4.3
8
833eee833A5.4
9
855fff855A2
 Sheet1 *

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

--------------------------------
what i want is that:
extract price from test_a.xls if values in columns A&C of test_b.xls are exactly matched with corresponding columns in test_a.xls.

tks.
CL.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•