Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
LCW LCW is offline
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 176
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
LCW LCW is offline
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
LCW LCW is offline
external usenet poster
 
Posts: 3
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup Formula: Return 1st match, then 2nd match, then 3rd match Scott Excel Discussion (Misc queries) 4 December 11th 09 06:50 AM
LookUp/Match/Index/OffSet Question carl Excel Worksheet Functions 4 December 21st 07 07:46 AM
Advanced Excel: offset, index/match, lookup, other? help!! ryguy7272 Excel Worksheet Functions 2 December 17th 06 11:51 AM
Double and Multiple Lookup Using the MATCH Function Charles793 Excel Worksheet Functions 0 May 11th 06 01:46 PM
lookup, index, match, offset, etc. [email protected] Excel Worksheet Functions 2 January 3rd 05 09:51 PM


All times are GMT +1. The time now is 10:53 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"