Offset/Match Double Lookup
Thanks so much Mike. It worked perfectly. It must be the name and
occupation, my Dad is a retired engineer named Mike. Thanks again.
"Mike H" wrote:
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.
"LCW" wrote:
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(Sh eet1!$B$4:$C$1234),2),0),-2)
Thanks
LCW
|