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

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History