VLOOKUP multiple conditions ...

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

Excel 2000 ... Good morning Board ...

How do I write VLOOKUP to compare multiple conditions? I
wish to compare ...

TabSheet1 Cols A & B against
TabSheet2 Cols A & E

Col A Tabsheet1 = Col A TabSheet2
Col B TabSheet1 = Col E TabSheet2

Thanks ... Kha
 
Hi
one way: try the following array formula (entered with
CTRL+SHIFT+eNTER):
=INDEX('sheet1'!$C$1:$C$100,MATCH(1,('sheet1'!
$A$1:$A$100=A1)*('sheet1'!$B$1:$B$100=B1),0))
 
Frank ... I know I had to miss something in my 1st post so
will attempt clarification ...

I need to use VLOOKUP ... I know how to use it, but only
on a single condition (look for this value in this
range) ... I need VLOOKUP to look for 2 values in a range
(2 separate columns).

TabSheet1 Col A = Part Number
TabSheet1 Col B = Op Number
TabSheet1 Col C = VLOOKUP formula

TabSheet2 Col A = Part Number
TabSheet2 Col E = Op Number

I wish to VLOOKUP to find PN & OP on TabSheet2 that
matches PN & OP on TabSheet1 ... then enter data into
TabSheet1 Col C from Col specified in VLOOKUP Formula ...

HTH ... Thanks ... Kha
 
Ken, assuming the following:

TabSheet1 Cell A1 = Part A1
TabSheet1 Cell B1 = Op Ken
TabSheet1 Cell C1 = VLOOKUP formula

TabSheet2 Cell A1 = Part A1
TabSheet2 Cell E1 = Op Ken

What are you looking to return into Cell C1? Is there a
value in another column in TabSheet2 that has the data for
which you are seaching?
 
Hi
this is why I provided you with a INDEX/MATCH combination
to replace vlookup. In your case enter the following array
formula (entered with cTRL+SHIFT+ENTER) in cell C1 on
worksheet 1:
=INDEX('sheet2'!$B$1:$B$100,MATCH(1,('sheet2'!
$A$1:$A$100=A1)*('sheet2'!$E$1:$E$100=B1),0))

This returns the data from column B of your second sheet
if column A+E matches your values
 
Formula = VLOOKUP
(lookup_value,table_array,col_index_num,range_lookup)

I know how to use this Formula with 1 lookup value ... How
would I write formula if I have 2 lookup values (PN &
OP)? For simplification ... Lets say all data is on same
TabSheet ...

Col A = PN
Col B = Op No

Col C = VLOOKUP formula I need

Col H = PN
Col K = Op No
Col P = Data I need to extract into Col C
(Note: Spread sheet Range = A:X)

If lookup value PN only then I would write formula as:

=VLOOKUP(A1,H:X,9,FALSE)

Issue is ... I have 2 lookup values (PN & OP).

Thanks ... Kha
 
To make the VLOOKUP function work I inserted a Column A on
Sheet2. Then I concatenated columns H and K into column A.
In A1 I entered "=H1&K1" and copied down column A.

Then the following function in C1 of Sheet1 should work:

=VLOOKUP(A1&B1,Sheet2!A100:Y100,12,FALSE)

HTH.
 
Frank ... This am I read your answer to later post on
VLOOKUP (same issue I had yesterday) ... Unfortunately,
yesterday when I read your response to my post and did not
see a variant of VLOOKUP I simply thought your INDEX/MATCH
formula was an answer to a different question & therefore
continued to try to clarify.

OK ... I have the message now ... & I will attempt (as you
suggest) to use INDEX/MATCH formula instead of VLOOKUP for
this issue.

Please pardon my oversight on your response to my original
post ... & THANK YOU & the many other Magicians who are
intimate with Excel for supporting this board ... KHA
 
Back
Top