lookup using two columns


H

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
 
Ad

Advertisements

F

Frank Kabel

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

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
 
Ad

Advertisements

H

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?!?!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

lookup field using two columns 4
Lookup a value using two columns 3
two column lookup 3
Two-Column Lookup 2
two-column lookup 7
Lookup Two Columns 5
Lookup two different columns 10
Difficult Two Column Lookup 0

Top