#1   Report Post  
Posted to microsoft.public.excel.misc
AZExcelNewbie
 
Posts: n/a
Default 2 condition vlookup

I need a 2 condition vlookup to lookup a date and when it matches the date
then it looks to match a state and then return a value on another worksheet.
Any ideas on how to do this?

example;
2/17/06 <---lookup this date
AZ value 1 <---Then lookup the state and then return value which is
on another worksheet
ID value 1
CA value 1
OR value 1
  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default 2 condition vlookup

You can use this kind of syntax:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

(still an array formula)

AZExcelNewbie wrote:

I need a 2 condition vlookup to lookup a date and when it matches the date
then it looks to match a state and then return a value on another worksheet.
Any ideas on how to do this?

example;
2/17/06 <---lookup this date
AZ value 1 <---Then lookup the state and then return value which is
on another worksheet
ID value 1
CA value 1
OR value 1


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP used only when IF condition is met Bradley Excel Worksheet Functions 3 November 22nd 05 05:32 PM
Vlookup using a range condition John Excel Worksheet Functions 4 August 26th 05 08:40 PM
VLOOKUP Limitations chris_manning Excel Worksheet Functions 2 August 9th 05 06:23 PM
vlookup in two condition val via OfficeKB.com Excel Discussion (Misc queries) 1 March 18th 05 08:12 AM
vlookup data hidden within worksheet Excel Worksheet Functions 0 January 26th 05 12:09 PM


All times are GMT +1. The time now is 02:23 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"