Home 
Search 
Today's Posts 
#1




Offset/Match Double Lookup
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 
#2




Offset/Match Double Lookup
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 
#3




Offset/Match Double Lookup
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  Regards, Dave "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 
#4




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 
#5




Offset/Match Double Lookup
Thanks Dave. You both had the same idea. Much appreciated, driving me nuts.
You guys rock!! "David Billigmeier" wrote: 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  Regards, Dave "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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Lookup Formula: Return 1st match, then 2nd match, then 3rd match  Excel Discussion (Misc queries)  
LookUp/Match/Index/OffSet Question  Excel Worksheet Functions  
Advanced Excel: offset, index/match, lookup, other? help!!  Excel Worksheet Functions  
Double and Multiple Lookup Using the MATCH Function  Excel Worksheet Functions  
lookup, index, match, offset, etc.  Excel Worksheet Functions 