lookup using two columns

[hef]

Is there a way to determine the following:

I'm looking for the date that corresponds to 153117 & 20

The table I'm looking in contains the following:

A B C
1 153115 20 07-18-2004
2 153116 10 07-21-2004
3 153117 10 07-20-2004
4 153117 20 07-18-2004

The answer is C4.

I know you can use Match and & to look up using two columns. But ever
time I try to create a formula, it comes back the the VALUE error...

Help!!!!!!!!!!!!!!!!!!!!!!!!!!

Thanks

 

[Frank Kabel]

Hi
try the array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(C1:C100,MATCH(1,(A1:A100=153117)*(B1:B100=20),0))

 

[Domenic]

Hi,

Try the following array formula which must be entered usin
CONTROL+SHIFT+ENTER...

=INDEX(C1:C4,MATCH(1,(A1:A4=D1)*(B1:B4=E1),0))

where D1 contains your first criteria and E1 your second

Hope this helps

 

[hef]

How can I copy/paste an array down a column.

Meaning...

I've now created the formula to look up two columns (thanks for th
help!!!)

The formula is
INDEX($J$2:$J$22735,MATCH(1,($F$2:$F$22735=a2)*($G$2:$G$22735=b1),0))

I've tried to highlight the entire area I need, enter the formula i
the first cell and then hit ctrl+shift+enter...but it doesn't work.
then tried copying/paste the traditional way, and that didn't wor
either.

help?!?!