Lookup

J

jlg5454

Hello,

I need a function that looks at two conditions and returns a value.
For example, I need a to look at cell H3 wich is CAPONE and also look
at cell a13 which is 1/7/06 and return a value from a range name -
apps.

I tried mixing up vlookups and match with no results.

Thanks,
 
G

Gary L Brown

If(And(H3="CAPONE",A13 = Datevalue("01/07/2006")),VLookup(xxx,yyy,###,###),"")
HTH,
 
J

jlg5454

It gives me the first match to the date. I want the exact match fro
the name range with the criteria. There are four columns in thi
range. First column is date, second is h3, third is something an
fourth is the data I want to return. Basically I want to lookup dat
based on columns 1 and 2 matching.

Thank
 
G

Gary L Brown

In that case, you need a column to concatenate the 2 values so that vlookup
can find both at the same time.
HTH,
 
V

vezerid

Assuming your lookup table has the following form:
A........B..............C
Date....Name....Value
1/7......Capone..18
etc

Then the following ARRAY formula will do:
=OFFSET($C$1, MAX(ROW(1:100)*--($A$2:$A$101=a13)*--($B$2:$B$101=H3)),
0)

This means that you must use the key combination Shift+Ctrl+Enter to
commit.

HTH
Kostis Vezerides
 
Top