Solved

# Lookup on 3 conditions

Posted on 2014-01-27
204 Views
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
Question by:Utredning
• 3
• 3

LVL 31

Expert Comment

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 31

Accepted Solution

Rob Henson earned 300 total points
See attacheed screen shot with additions as suggested above.
Screen-Shot.png
0

LVL 8

Assisted Solution

itjockey earned 200 total points
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))
``````

Thanks
0

LVL 8

Expert Comment

I guess
``````=DGET(A1:G11,E16,F15:G16)
``````
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))
``````
0

Author Closing Comment

Thanks alot!
0

LVL 31

Expert Comment

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

Thanks for explanation.
0

## Featured Post

### Suggested Solutions

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
My experience with Windows 10 over a one year period and suggestions for smooth operation