A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Offset/Match Double Lookup



 
 
Thread Tools Display Modes
  #1  
Old February 23rd 10, 10:08 PM posted to microsoft.public.excel.worksheet.functions
LCW
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

Ads
  #2  
Old February 23rd 10, 10:19 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 11,504
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  
Old February 23rd 10, 10:19 PM posted to microsoft.public.excel.worksheet.functions
David Billigmeier
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  
Old February 24th 10, 01:47 AM posted to microsoft.public.excel.worksheet.functions
LCW
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  
Old February 24th 10, 01:48 AM posted to microsoft.public.excel.worksheet.functions
LCW
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
> >

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 05:50 AM
LookUp/Match/Index/OffSet Question carl Excel Worksheet Functions 4 December 21st 07 06:46 AM
Advanced Excel: offset, index/match, lookup, other? help!! ryguy7272 Excel Worksheet Functions 2 December 17th 06 10: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 08:51 PM


All times are GMT +1. The time now is 11:11 PM.


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