Excel - Offset/Match Double Lookup

Asked By LCW on 23-Feb-10 05:08 PM
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 have been interrupted so many times, I do not know where I am at.  This was my
formula but....  D13 would be the order# and S13 would be the dispatch#.  In
my range, I do not have any col headings, it looks just like above.  We have
Excel 2003.



Mike H replied to LCW on 23-Feb-10 05:19 PM

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


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 cannot type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
David Billigmeier replied to LCW on 23-Feb-10 05:19 PM
Try this:


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

I am assuming that 7 digit number is the dispatch number, if not switch
around S13 and D13 in the formula
LCW replied to Mike H on 23-Feb-10 08:47 PM
Thanks so much Mike.  It worked perfectly.  It must be the name and
occupation, my Dad is a retired engineer named Mike.  Thanks again.
LCW replied to David Billigmeier on 23-Feb-10 08:48 PM
Thanks Dave.  You both had the same idea.  Much appreciated, driving me nuts.
You guys rock!!