Let me start by describing my excel problem: basically I need to find value which match 2 conditions in different worksheet.

I have 2 worksheets (WK1 and WK2).

WK1 has 100 rows with 100 value in column A.

WK2 has 1000 rows with 100 value in column A; 1000 rows with 3 value (let say X, Y, Z) in column B; 1000 rows in column C

I am going to populate the result in column B, C and D in WK1.

Column B(WK1) has to find matching value in column A(WK2) and X in column B(WK2) and return the result from column C(WK2). Column C(WK1) has to find matching value in column A(WK2) and Y in column B(WK2) etc.I am thinking about this formula, but it doesn't work

AND(VLOOKUP(A1,WK2!A1:B1000,3,FALSE),VLOOKUP(X,WK2!A1:B1000,3,FALSE)Your help is greatly appreciated !

Anton

First, unrelated to your goal, the construction of your VLOOKUP's are wrong. Your

table_arrayof A1:B1000 is 2 columns wide, yet you asking VLOOKUP to return a value from column 3 of the array. Even if the AND method you are trying would work (which it won't) the VLOOKUPS will fail with a #REF error.Your VLOOKUP should look like this, since a

lookup_arrayof A1:C1000 is 3 columns wide.=VLOOKUP(A1,WK2!$A$1:$

C$1000,3,FALSE)or

=VLOOKUP(A1,WK2!$A$1:$

C$1000,3,0) since FALSE and 0 are the same thing to Excel.OK, so back to your problem...

One possible method is to use a helper column in WK2 and use that for the VLOOKUP.

Let's say you have this in WK2:

A B C 1 Tom X 4 2 Bill Y 7 3 Sue X 9Insert a new Column A and drag this formula down:

=B1 & C1

to get:

A B C D 1 TomX Tom X 4 2 BillY Bill Y 7 3 SueX Sue X 9Now hide Column A if you want.

For your VLOOKUP in WK1, use:

=VLOOKUP(A1&"X", WK1!$A1:D3, 4, 0)

This will use the values in Column A concatenated with an X (or Y) and look them up in Column A of WK2 (TomX, BillY, etc.) and then return the value from Column D, which will be your old Column C.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Dear DerbyDad, Your solution solves my problem !!!

I am very grateful that you take time and explain it in details.Many thanks,

Anton

Ask Your Question

Weekly Poll