Lookup on 3 conditions

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
UtredningAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
See attacheed screen shot with additions as suggested above.
Screen-Shot.png
0
 
Rob HensonFinance AnalystCommented:
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
 
Naresh PatelConnect With a Mentor TraderCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Naresh PatelTraderCommented:
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
 
UtredningAuthor Commented:
Thanks alot!
0
 
Rob HensonFinance AnalystCommented:
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
 
Naresh PatelTraderCommented:
Thanks for explanation.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.