Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Lookup on 3 conditions

Posted on 2014-01-27
7
212 Views
Last Modified: 2014-01-27
Hi all,

Enclosed you will find a woorkbook that contains a table that holds a value given 3 conditons.

In collumn A: Date
In collumn B: Period
and in Row 1: Currency

Could you pls provide me with a lookup-function that returns the value for any given set of conditions?

Thank you :)
Lookup-on-3-conditions.xlsx
0
Comment
Question by:Utredning
  • 3
  • 3
7 Comments
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39811427
Add headers to columns A & B for Date and Period and the same headers above your criteria cells, then you can use:

=DGET(A1:G11,E16,F15:G16)

Where A1:G11 is table including new headers as described above, E16 is cell containing Currency and F15:G16 contains headers the same as Date and period columns and the date and period criteria.

Thanks
Rob H
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 300 total points
ID: 39811437
See attacheed screen shot with additions as suggested above.
Screen-Shot.png
0
 
LVL 8

Assisted Solution

by:itjockey
itjockey earned 200 total points
ID: 39811438
Hi Utredning,


copy past this formula & click to fx bar & then all keys to gather ctrl+shift+enter.
=INDEX(A1:G11,MATCH(F16&G16,A1:A11&B1:B11,0),MATCH(E16,A1:G1,0))

Open in new window


Thanks
0
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
LVL 8

Expert Comment

by:itjockey
ID: 39811446
I guess
=DGET(A1:G11,E16,F15:G16)

Open in new window

much suitable then complex array formula. thanks Mr. Rob H  this is adding some thing new in my knowledge. :)
=INDEX(A1:G11,MATCH(F16&G16,A1:A11&B1:B11,0),MATCH(E16,A1:G1,0))

Open in new window

0
 

Author Closing Comment

by:Utredning
ID: 39811452
Thanks alot!
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39811459
DGET only works because of the format of the data.

If multiple entries match the criteria specified then formula will give an error, whereas I believe INDEX will return first entry that matches.

For purpose of clarification for  both, syntax of DGET (and other Dfunction) formula:

=DGET(Database,Field,Criteria)

In this instance, the currency happens to be a Field header so can be used to determine the column but needed the other headers added to identify fields or combinations thereof.

Thanks
Rob H
0
 
LVL 8

Expert Comment

by:itjockey
ID: 39811474
Thanks for explanation.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question