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(Sheet1!$B$4:$C$1234),2),0),-2)

Thanks
LCW

0
Utf
2/23/2010 10:08:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
765 Views

Similar Articles

[PageSpeed] 18

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(Sheet1!$B$4:$C$1234),2),0),-2)
> 
> Thanks
> LCW
> 
0
Utf
2/23/2010 10:19:01 PM
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(Sheet1!$B$4:$C$1234),2),0),-2)
> 
> Thanks
> LCW
> 
0
Utf
2/23/2010 10:19:02 PM
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(Sheet1!$B$4:$C$1234),2),0),-2)
> > 
> > Thanks
> > LCW
> > 
0
Utf
2/24/2010 1:47:01 AM
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(Sheet1!$B$4:$C$1234),2),0),-2)
> > 
> > Thanks
> > LCW
> > 
0
Utf
2/24/2010 1:48:05 AM
Reply:

Similar Artilces:

Lookup Help... Again.
You guys were so great last time here is another one for you... I have two 'scorecards' with an id number, name, and score. Both are sorted by score in descending order. I would like to generate cumulative scorecard with both scores and a summary score but when I try and look up the score up based upon the ID I am getting incorrect results. There is a match for every name so it isn't a a null error. The formula I'm using is as follows... =IF(ISNA(LOOKUP($E43,'Phase I Scorecard'!$E$13:$E43,'Phase I Scorecard'!$H$13:$H43)),"",LOOKUP($E43,...

Counting records matching criteria
I have the following queries that count records matching particular criteria. One counts ProdPrefix="C" and the other counts ProdPrefix="M". Output is in 2 columns for each query as follows DonorName ProdPrefixC DonorName ProdPrefixM How can I put the whole lot together to get an output with 3 columns as follows DonorName ProdPrefixC ProdPrefixM SQL generated in query design for each query. SELECT DonatedProducts.DonorName, Count(DonatedProducts.ProdPrefix) AS CountPrefixC FROM DonatedProducts WHERE (((DonatedProducts.ProdPrefix)="C"...

Broke mail
Whats causing this ? only thing in the event viewer is Event Type: Warning Event Source: Marc3Host Event Category: None Event ID: 6204 Date: 21/02/2007 Time: 17:54:30 User: N/A Computer: XXXXXX Description: Plugin DSScheduler reports exception. Data Sources: Builder failed. Additional info: Could not resolve url For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. been trying to remove a 2007 server but its infected everything it seems Marc3Host? What's that? You've probably got something else installed on the Exchange server....

Count condition true for 3 lookup columns
Hi All, I have a worksheet that contains 3 seperate lookup columns. My challenge is that I need a low overhead way to find and count the combination of true lookups. So if one of the 3 looksup is found I would get a return of '1', if 2 of the 3 are found then '2'. I know I can do this with an array formula, but am worried about the calculation hit as this is a very complex worksheet and already has a long intitial calc time. I suppose I could do the inverse and could an 'iserror' condition and then subract that answere from 3. I thought of using a complex 'if&...

Issue Printing Double-sided
I have a Excel file that when I try and print all tabs (50 tabs / 140 pages) the Lanier printer wont't print double sided (defaults to single side) *** Yet when I print the first few tabs, it does pring "Double-Sided/Duplex".**** What may be happening later in the file that causes the printer "not" to print duplex? The file uses the same print quality, page size and oreintation. Thanks ...

Lookup for once #2
Thanks Dave for your help. But is there a way that you could input a formula instead of having the advanced filter? Thank you, Kevin ------------------------------------------------------ Maybe Data|Filter|Advanced filter|Unique Records only. See Debra Dalgleish's site for nice instructions. http://contextures.com/xladvfilter01.html#FilterUR Kevin Lin wrote: > > Hi All, > > Need help on the following. > > A V > 1 AAA001 AAA001 > 2 AAA001 BBB001 > 3 AAA001 BBB002 > 4 BBB001 > 5 BBB001 > 6 BBB002 &...

Setting up page layout for a5 to double up on a4 paper
Hi Does anyone know if there is a way to set up an document so that you can print it an A5 document on to A4 paper and get the A5 repeated twice on each page. Hope that makes sense Thanks Assuming this is Word 2000 or later, on the Paper tab of Page Setup, select A4 as the paper size. On the Margins tab, under "Multiple pages," select "2 pages per sheet." If you create the A5 page only once, then you'll need to put "1,1" in the Pages box in the Print dialog. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope,...

Zip code lookup
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I want to enter a zip code in sheet 1 column D and return a county name from sheet 2 to sheet 1 column E. Help! On 3/22/10 6:24 PM, pastortom@officeformac.com wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: > Intel I want to enter a zip code in sheet 1 column D and return a county > name from sheet 2 to sheet 1 column E. Help! Assuming that you have table of county names and zip codes in sheet 2, then you simply need a hlookup formula in each cell of column E in...

Reverse DNS Lookup
I think I've solved my relaying issue with my Exchange 2003 server but I was just reported again and the message indicates that I am open for relaying. when I run the rcpt to: anymail@fakedomain.com command, the system tells me that no relaying is allowed. That said, I think outside hosts are having trouble talking to my mail server via reverse DNS lookup. I know for a fact that when I go home and try to telnet to my exchange public IP, it takes a while to respond and nothing happens when I send the helo test command. Can some one explain how this can be fixed and if this is a factor? ...

Matching cells #4
A1 thru A10 contains sequentially-sorted data. B1 thru B6 contains sequentially-sorted data which matches SOME (but not ALL) of the data in Col A. For example. A B 1 1 1 2 2 3 3 3 4 4 4 6 5 5 8 6 6 10 7 7 8 8 9 9 10 10 I need to move the data in the cells in Col B next to the cells in Col A that have the same contents. For example: A B 1 1 1 2 2 3 3 3 4 4 4 5 5 6 6 6 7 7 8 8 8 9 9 10 10 10 In my actual spreadsheet, Col A contains 60,000 ce...

Matching names and inserting spaces
I hope someone can help me out with this. Say I have column A filled with like 500 names, and column B filled with 300 of the same names. Is there a way or a formula to match the names in column B to the corresponding names in column A, move the name in B to be in the same row as the name in A, and to insert blank cells into B where there are no names to match A? Example: A B Abe Abe Brad Brian Brian Chris Chris changed to: A B Abe Abe Brad Brian...

Total Value and Account Value do not match??
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I am using Microsoft Money 2004 Standard. I just set up a new account to keep track of mutual funds however even with just one transaction listed I get a weird total figure. Here are the figures: Shares: 1593.298 Price: $10.06 Total Value: $16028.58 Except Money says the "Account Value' is $16140.11 Where does the difference come from? How do I get the numbers to match up? Thanks - -- Due to large amount of spam mail as well as personal security, please respond directly to this newsgroup. Encrypted Messages, using PGP can be poste...

Transaction Matching
I need a GL Transaction Matching Report to show me the not matched transaction only. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=4b9c44a3-896a-4bd7-810b-fdc80d429fea&am...

Match value in one column and return value in same row in next col
Hi, I have a a set of values in A1 through A100. I need to look up each value and find a match in another set of values located in C1 through C200. If a match is found then I need the formula located in column B to return the value in the same row but the next column over (D). Example: A B C D 359 375 10012 456 125 10031 125 10031 952 10052 854 444 10017 The formula located in B3 should find that the value of 125 (located in A3) matches the value in C2 and returns 10031 which is the v...

function to check if string contains double byte characters
is there a function that i could use to check if a string contains any double byte characters? I have tried IsDBCSLeadByteEx(), but using this function inside a for loop can really slow down your app so I am looking for a function that checks instantly whether the string contains double byte characters or not.... thanks!! Would IsTextUnicode() method help you out? or maybe _ismbclegal() AliR. "MFC" <MFC@discussions.microsoft.com> wrote in message news:A0C78D89-CF79-42D7-BB4B-8C3DAA0AA720@microsoft.com... > is there a function that i could use to check if a string con...

RMA/RTV Lookup by Item Number
In Great Plains 9.0, we would like criteria to search by Item Number in the RMA and RTV Lookup windows. Even though the systems allows multiple lines on RMAs and RTVs, we would still like to find the RMA and RTV number by item number. In many circumstances with have an item number but not the RMA or RTV number. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggesti...

help with lookup #2
I have a workbook made of 2 sheets. Sheet1 has a list of dates down column B (starting at row 4). Thes dates are in chronological order. Sheet2 has a list of anniversaries down Column C (starting at C1) tha are also in chronological order, with a text entry for each date in th adjacent cell in column D. I want my spreadsheet to automatically report into column D of sheet the text entries from column D of sheet2 alongside the appropriat date. In other words, Sheet1!$B$4 contains 1/Jan/2004 Sheet2!$C$1 contains 1/Jan/2004 Sheet2!$D$1 contains New Years Day I want my spreadsheet to reco...

multiple database lookup
I'm getting myself totally tied in knots over this one. Database sheet holding: order number, workstation, date, operator, quantity produced. An order may take up several days of production at the same workstation, sometimes on two shifts. On a separate sheet I have the list of currently running orders and the production manager wants to see how many parts were produced by which operators on the most recent day worked. A straight lookup based on the order number won't do it because multiple rows will meet that criteria. I need to get all those rows and then pick out j...

Additional lookup for contacts on opportunity form
Hi, I try to customise CRM 3.0 without any training - therefore a question for which I need some help. On the opportunity form exists a look-up (potentional customer) for contacts/accounts. I'd like to add an aditional one to the form (to replace the decision maker). I tryed to create a new field on the opportunity entity but there's no field-type "look-up". It's also not possible to create a new relationship to the contact entity (both system entity's). Does anybody of you know how I can do this? (if yes - please explain it more detailed...). Thanks Joerg Look...

Compare entries in 2 worksheets and list what does not match
Good Day All; I have 2 Excell works sheets with approx 14000 rows each. What I would like to do is compare both lists and get a 3rd list that shows what entries do not match. Is there a simple way to do ythis in Excel Thanks All Chomp Assuming A1 should equal A1 in the other sheet... =IF(Sheet1!A1=Sheet2!A1,"",Sheet!A1&" does not match "&Sheet2!A1) Auto-Filter for non-blanks. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "The Chomp" wrote: > Good Day All; > > I have 2 ...

Index Match
Anybody ever used the Index and Match functions together to do lookups All the time. In article <40daee1c$1@news.>, "Provident User" <Provident_User@providentcompanies.com> wrote: > Anybody ever used the Index and Match functions together to do lookups ...

Function for Match
Hi All I have posted in programming my problem , and maybe it can be done with a formula . Please for help . Here is my the description of my problem : http://groups.google.ro/group/microsoft.public.excel.programming/browse_thread/thread/3924d69370920eb4# >maybe it can be done with a formula . No, that can't be done with formulas. -- Biff Microsoft Excel MVP "ytayta555" <wherewindsmeet@gmail.com> wrote in message news:0a069dc4-b97d-4562-8ff0-49448b8423ea@g10g2000yqh.googlegroups.com... > Hi All > > I have posted in programming my pr...

Enter/Match Invoice vendor is different than who is paid
We've just started using the 3-way matching process, which we thought was going to be a big plus for us. However, we just ran into a situation that we wonder if anyone else has encountered: A PO is created for Vendor A. However, we're billed by Vendor B and we must issue a check to Vendor B. When we do the Enter/Match Invoice process we have to use Vendor A or we don't get a match. However, this sets up a payable to Vendor A, when in reality we need to pay Vendor B. This would seem to call for some sort of a vendor National Account feature, which, as far as I know, isn'...

Transaction Matching #3
All of a sudden, everytime I download recent transactions from my Bank, Money duplicates the entry. Furthermore, if Money associates the transaction to a future occurence of an event, it will not let me change it. Lastly, if I try to match a downloaded transaction to one that I have already entered, Money will not list it in the "Change Transaction Matching" screen. Help! In microsoft.public.money, stiglo wrote: >All of a sudden, everytime I download recent transactions from my Bank, Money >duplicates the entry. Furthermore, if Money associates the transaction to a ...

code for double click criteria
Greetings, I am trying to get this piece of code to work. I want to double click a name in a subform and have the record in another form open. tblContributors. [ContributorID] is the name of the hidden primary key field of the table that holds the data for the form I want to open. So I make a function named ViewDonor and then call it in Sub txtDonorLastName_DblClick. I thought that the DoCmd.OpenForm should work. Obviously I am wrong. I get an error that says "variable not defined" and it highlights the second tblContributors. Anyone know what I am doing wrong? Anyone bu...