Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |