Solved Excel Formula with VLOOKUP and Logical Function

Microsoft Excel 2007 (upgrade)
July 12, 2012 at 09:00:28
Specs: Windows XP
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

Your help is greatly appreciated !


See More: Excel Formula with VLOOKUP and Logical Function

Report •

July 12, 2012 at 10:50:56
✔ Best Answer
First, unrelated to your goal, the construction of your VLOOKUP's are wrong.

Your table_array of 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_array of A1:C1000 is 3 columns wide.



=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      9

Insert 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     9

Now 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.

Report •

July 12, 2012 at 11:24:09
Dear DerbyDad,

Your solution solves my problem !!!
I am very grateful that you take time and explain it in details.

Many thanks,

Report •

Related Solutions

Ask Question