Offset/Match Double Lookup

  • Thread starter Thread starter LCW
  • Start date Start date
L

LCW

Trying to do a double lookup.

4931857 1 CO
4931857 2 LO
4931890 1 CO
4931890 2 LO
4931890 3 LO

Want to look up the first two columns and get the third column as answer.
I've been interrupted so many times, I don't know where I'm at. This was my
formula but.... D13 would be the order# and S13 would be the dispatch#. In
my range, I don't have any col headings, it looks just like above. We have
Excel 2003.

OFFSET(Sheet1!$A$4:$C$1234,MATCH(D13,OFFSET(Sheet1!$A$4:$C$1234,0,0,ROWS(Sheet1!$A$4:$C$1234),3),0)-0,MATCH(S13,OFFSET(Sheet1!$B$4:$C$1234,0,0,ROWS(Sheet1!$B$4:$C$1234),2),0),-2)

Thanks
LCW
 
Hi,

Try this ARRAY formula and see below on how to enter it. I have assumed your
data are in Col's A,B & C with the lookup values for Col A (D13) and Col B
(S13)

=INDEX(C1:C20,MATCH(1,(A1:A20=D13)*(B1:B20=S13),0))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Try this:

=INDEX($C$4:$C$1234,MATCH(S13&D13,$A$4:$A$1234&$B$4:$B$1234,0))

Commit this with CTRL+SHIFT+ENTER, as it's an array formula

I'm assuming that 7 digit number is the dispatch number, if not switch
around S13 and D13 in the formula
 
Thanks so much Mike. It worked perfectly. It must be the name and
occupation, my Dad is a retired engineer named Mike. Thanks again.
 
Back
Top