Match 2 Columns, Return 3rd, Differing Match Types

Hello,

I have 3 columns of data in my main table.  For example:

Blue / 2 / $5
Blue / 4 / $10
Blue / 6 / $15
Green / 3 / $4
Green / 10 / $8
Red / 1 / $1
Red / 4 / $3

What I am looking to do is use some kind if Index/Match function (I
think) to look up the color (column A) and number (column B) and have
the price returned (column C).  I have found lots of solutions for
this out there, however the one part I keep getting stuck on is when I
need to look up something like the following...

Blue / 5 = #N/A.  I want the answer to be $10 but since the number 5
is not an exact match with 2, 4 or 6 an error is returned.

How can I set up a formula where it is using a match type of 1,
instead of the exact match on the number lookup portion (column b).

Maybe I need to take a totally different route with the whole thing?
Thank you in advance for any suggestions you are able to provide!!
0
Matt
5/10/2010 3:09:46 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
1693 Views

Similar Articles

[PageSpeed] 11

Hi,

=INDEX(C1:C4,MATCH(E1,A1:A4,0)*MATCH(F1,B1:B4,1))

change range to fit your needs

"Matt.Russett" wrote:

> Hello,
> 
> I have 3 columns of data in my main table.  For example:
> 
> Blue / 2 / $5
> Blue / 4 / $10
> Blue / 6 / $15
> Green / 3 / $4
> Green / 10 / $8
> Red / 1 / $1
> Red / 4 / $3
> 
> What I am looking to do is use some kind if Index/Match function (I
> think) to look up the color (column A) and number (column B) and have
> the price returned (column C).  I have found lots of solutions for
> this out there, however the one part I keep getting stuck on is when I
> need to look up something like the following...
> 
> Blue / 5 = #N/A.  I want the answer to be $10 but since the number 5
> is not an exact match with 2, 4 or 6 an error is returned.
> 
> How can I set up a formula where it is using a match type of 1,
> instead of the exact match on the number lookup portion (column b).
> 
> Maybe I need to take a totally different route with the whole thing?
> Thank you in advance for any suggestions you are able to provide!!
> .
> 
0
Utf
5/10/2010 4:03:01 PM
One way...

*IF* the data is grouped by color then sorted in ascending order by column 
B.

Assuming the data is in the range A2:C8

E2 = lookup color
F2 = lookup number

=LOOKUP(F2,OFFSET(B2:C2,MATCH(E2,A2:A8,0)-1,,COUNTIF(A2:A8,E2)))

-- 
Biff
Microsoft Excel MVP


"Matt.Russett" <matt.russett@gmail.com> wrote in message 
news:6381519c-3dab-477e-9599-7b89e9531054@e2g2000yqn.googlegroups.com...
> Hello,
>
> I have 3 columns of data in my main table.  For example:
>
> Blue / 2 / $5
> Blue / 4 / $10
> Blue / 6 / $15
> Green / 3 / $4
> Green / 10 / $8
> Red / 1 / $1
> Red / 4 / $3
>
> What I am looking to do is use some kind if Index/Match function (I
> think) to look up the color (column A) and number (column B) and have
> the price returned (column C).  I have found lots of solutions for
> this out there, however the one part I keep getting stuck on is when I
> need to look up something like the following...
>
> Blue / 5 = #N/A.  I want the answer to be $10 but since the number 5
> is not an exact match with 2, 4 or 6 an error is returned.
>
> How can I set up a formula where it is using a match type of 1,
> instead of the exact match on the number lookup portion (column b).
>
> Maybe I need to take a totally different route with the whole thing?
> Thank you in advance for any suggestions you are able to provide!! 


0
T
5/10/2010 4:10:09 PM
Just in case the data isn't always grouped and sorted:

=INDEX($C$2:$C$8,
MATCH(MAX(INDEX(($A$2:$A$8=E2)*($B$2:$B$8<=F2)*$B$2:$B$8,)),
INDEX(($A$2:$A$8=E2)*$B$2:$B$8,),0))

HTH
Steve D.


"T. Valko" <biffinpitt@comcast.net> wrote in message 
news:ufZmEtF8KHA.5808@TK2MSFTNGP02.phx.gbl...
> One way...
>
> *IF* the data is grouped by color then sorted in ascending order by column 
> B.
>
> Assuming the data is in the range A2:C8
>
> E2 = lookup color
> F2 = lookup number
>
> =LOOKUP(F2,OFFSET(B2:C2,MATCH(E2,A2:A8,0)-1,,COUNTIF(A2:A8,E2)))
>
> -- 
> Biff
> Microsoft Excel MVP
>
>
> "Matt.Russett" <matt.russett@gmail.com> wrote in message 
> news:6381519c-3dab-477e-9599-7b89e9531054@e2g2000yqn.googlegroups.com...
>> Hello,
>>
>> I have 3 columns of data in my main table.  For example:
>>
>> Blue / 2 / $5
>> Blue / 4 / $10
>> Blue / 6 / $15
>> Green / 3 / $4
>> Green / 10 / $8
>> Red / 1 / $1
>> Red / 4 / $3
>>
>> What I am looking to do is use some kind if Index/Match function (I
>> think) to look up the color (column A) and number (column B) and have
>> the price returned (column C).  I have found lots of solutions for
>> this out there, however the one part I keep getting stuck on is when I
>> need to look up something like the following...
>>
>> Blue / 5 = #N/A.  I want the answer to be $10 but since the number 5
>> is not an exact match with 2, 4 or 6 an error is returned.
>>
>> How can I set up a formula where it is using a match type of 1,
>> instead of the exact match on the number lookup portion (column b).
>>
>> Maybe I need to take a totally different route with the whole thing?
>> Thank you in advance for any suggestions you are able to provide!!
>
> 

0
Steve
5/11/2010 9:45:25 AM
Reply:

Similar Artilces:

How is Return for Period calculated?
I am using Money 2003 and I did a Performance by Investment Account report for 2004. For most of stocks the Return for Period column is the Realized Gain/Loss + the Gain/Loss of your current holdings. However for some stocks the numbers don't seem to add up. For instance the realized gain is $400, gain of what I currently have invested is $500, and the return is reported as about $100. Could someone explain to me how this is calculated or if it's a bug. Thanks! Jon ...

"TOOLS" menu missing from menu bar #2
"TOOLS" menu is completely missing from the menu bar in Publisher 2003. How do I put it back? Might try a "detect and repair" Meanwhile, right-click a toolbar, click customize, scroll down to built-in menus, drag the tools menu to your toolbar. -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Anil Gupta" <AnilGupta@discussions.microsoft.com> wrote in message news:5C7ADC1E-A6E9-4F01-8C06-01B92AC7B97B@microsoft.com... > "TOOLS" menu is completely missing from the menu bar in Publisher 20...

Creating a Combination Bar Chart with 2 Lines
I am trying to create a bar chart with 2 line series. The Y-axis for the vertical bars would be on the left and the Y-axis for the 2 line series would be on the right (3 series were defined). The horizontal axis for all series are calendar months (Jan, Feb, etc.) Every time I setup the chart my chart only displays a single line, ignoring the other line series. The vertical bars come out fine. Any thoughts on what I am doing wrong? Thanks Bill Falzone Don't use the built in types. Make the chart using all columns. Select a series that you want to change (click on it right in th...

Vista, MS Access 2003 and MS ADO Ext 6.0 vs 2.x
I am running into an issue with MS Access 2003 installed on Vista. Apparantly in the MS ADO Ext 6.0 version either (format, round, or sum) is not in that DLL. However, when I link to v2.8 of the DLL, I don't have an issue. I am getting the following error message when running my query. "Function is not available in expressions in query expressions." Does anyone have other workarounds? I am distributing this application in an MDE format, so I was wondering if the end users may experience issues, if I link to my version of msadox.dll instead of the installed version. I tried s...

URGENT!!! Problem with row data being truncated in a copy worksheet sub #2
Dave, Thanks for responding. I tried this but I could not get it t work in conjunction with the entire module. It dies right afte copying and PasteSpecial Values It does not kill the temp file or loa the newly created sheet into an e-mail. Any Ideas -- Doctor ----------------------------------------------------------------------- DoctorV's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=514 View this thread: http://www.excelforum.com/showthread.php?threadid=26863 You may want to post your current procedure. DoctorV wrote: > > Dave, Thanks for respon...

Cannot Start Outlook #2
I have just run the auomatic windows update on my local PC and now Outlook 2000 will not start. It just hangs at the splash screen. Outlook is configured to connect to Exchange. Other PCs on the same Lan which have not been updated can still connect. The updates installed were 824141, 823182, 826232, 825119, 828035, 828749. I have unistalled all of these updates and Outlook still will not start. Any ideas? Does it start if you run this command from the command line, go to Start > Run > type "outlook /safe" without the quotes but with the space before the Forward slash &...

2 Email domains w/2 Act. Dir. Domains - Same Forest
Hello, Scenario: We've had domain1.com (AD) setup and running in exchange 2003 for a year now w/mixed win2k and win2k3 servers - email domain name is the same, domain1. OWA works great and haven't had any problems. Our company is branching out into a new business segment and I've created a new active dir. domain within the same forest, domain2.local. It is NOT a child domain, but does sit under the same forest. I need to configure my front end/back end exchange 2003 boxes to do the following: -receive email for both email domains (domain1.com and domain2.com) -let users...

Search Multiple Worksheets #2
Is there a way to search trough multiple worksheets for a specific value? Other posts have mentioned to use VBA, but I have never used that before. If anyone can give me some advice on using that or a type of formula to perform that can search multiple worksheets. Thank You There may be other ways but, while holding down the ctrl key select each of the worksheet tabs you want to search in then select Edit|Find from the menu. Type in the value you want and it will go to the first instance of that value. Now if you are wanting to preserve a specifice value for reference or ???, then...

Match formula to match values in multiple columns
Hi all, does any friend know that how can I make below formula work MATCH(A2,$K$2:$M$30,0) I am not sure I have understood correectly. Please go through the below example With data as below if you need to retrive the name of the 1st Rank holder from London. D2 = 1 D3 = London In D4 apply the below formula =INDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=D2)*($C$2:$C$9=D3),0)) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar'...

Difference Between Contacts and Address Book
I'm just trying to start to use Outlook (MS Small Bus 2007 w/ Outlook and Bus. Contact Mgr) for email and have a number of Contacts setup in different Categories. WHAT IS THE DIFFERENCE IN USING "Contacts" VERSUS USING THE ADDRESS BOOK??? -- CFL Joe There is no address book in Outlook. What you think is an address book is simply a view of the electronic addresses contained in your Contacts Folder. Explain why you are confused and what you question is. -- Russ Valentine [MVP-Outlook] "CFLJoe" <CFLJoe@discussions.microsoft.com> wrote in message news:06ED2E...

Different background color depending on x-axis value
Hi! Is there any way to have the background in a chart have differen colors depending on x-axis values? Like x-axis value 1-10 => blue background x-axis value 10-20 => red background Thank -- RealRave ----------------------------------------------------------------------- RealRaven's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3705 View this thread: http://www.excelforum.com/showthread.php?threadid=56778 Here are a few of the ways you can get custom background fills for your charts" http://PeltierTech.com/Excel/Charts/ChartIndex.html#Backgro...

print area #2
i cannot clear print area. when i select the print area and 'file', 'print area',the options are not available. any suggestions? When you use this command, Excel creates a range "print_area". Try deleting that range. Stan Scott New York City "bronxxbabe" <anonymous@discussions.microsoft.com> wrote in message news:78dc01c476a2$31509280$a401280a@phx.gbl... > i cannot clear print area. when i select the print area > and 'file', 'print area',the options are not available. > any suggestions? Perhaps you have two or more ...

HTTP Error 401.2
Hi, I have installed MS CRM 1.2 on my Domain controller running windows 2003 standard server and email router on exchange 2003. My local ip subnet is 172.0.0.x With ms vpn connection, I can access to ms crm by using outlook 2003 or internet explorer by opening the default ms crm page remotely. However, if i connect to ms crm with different subnet 10.0.0.x, i cannot connect to ms crm. 10.0.0.x subnet is routed to 172.0.0.x, where my users normally login email from the exchange 2003. What puzzle me is why i can authenticate from exchange 2003 but not ms crm server on using the same...

backup same set of servers from 2 DPM 2007 servers
I am changing the DPM server 2007. However, I would like to take this opportunity to revise all protection groups so that I can restore bare-metal any server from a single lTO-4 tape if possible. During this "transitional period," I would like to leave the existing backup settings on the older DPM server to continue to run while adding jobs to the new DPM server. Looks like to me that I have to re-install the agent on all servers so that they can be recognized by the new DPM server. IS there a better way to do this if my plan is possible at all? Thanks Bill ...

creating a function for dividing two columns
I am trying to keep track of softball stats. I trying to make a column of batting averages. I have at bats in column b, hits in column c with the results in column d. I cant get the function right so it will display the correct result. use this =SUM(C1/B1) in cell D1. Format D1 as a number with 3 decimal places. Steve Hubbard "cflan" <cflan@discussions.microsoft.com> wrote in message news:212BF952-55B9-45DF-BC8C-E8B670FA6010@microsoft.com... > I am trying to keep track of softball stats. I trying to make a column of > batting averages. I have at bats in column b,...

moving publisher #2
I bought a computer with publisher bundled (no cd). Now I want to put it on my new laptop. How do I move it from the desktop computer to the laptop???? Cliff without the source CD...you don't. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression "Cliff" <Cliff@discussions.microsoft.com> wrote in message news:336C1CBE-2A51-4B62-B83D-46C35C557B74@microsoft.com... > I bought a computer with publisher bundled (no cd). Now I want to put it > on > my new laptop. How do I move it from the desktop computer to the > laptop???? > > Cliff I...

Average speed #2
Hi all Having trouble working out average speed. miles in a1 =14.3 time in B1 = 0:50:23 this cell has been formatted to HH:MM:SS The formula A1/B1 brings back the wrong answer if I alter to A1/(B1*24) still does not work correctly. Advise please. Thanks Jon "jon" <jon@nospam.com> schrieb im Newsbeitrag news:4659eb9a$1_1@glkas0286.greenlnk.net... > Hi all > Having trouble working out average speed. > > miles in a1 =14.3 > time in B1 = 0:50:23 this cell has been formatted to HH:MM:SS > > The formula A1/B1 brings back the wrong answer if I al...

Keeps asking for password #2
I have followed all of the instructions given previously, I have deleted and recreated my e-mail account but Outlook 2002 (Windows XP) still keeps asking me for user ID and password every time it tries to send / recieve. The strange thing is that I have two accounts and this is only a problem with one of them. one of your accounts is set to ask formy outgoing server requires authentication, see under tools ...accounts... send...and then advanced email settings, or just double click on the email accunt , it wil give you the properties, take it from there it should be the settings should ...

Y Axis #2
I have two questions regarding the 'y' axis - in a normal line graph Firstly, how do i get the 'y' axis to appear in the middle of the graph IE to cross the 'x' axis at (0,0) instead of currently (in my graph) (-10,0) Secondly how do i plot the line "X =2" which should just be a vertical line on the graph? I'm using Microsoft Excel 2007 any help would be appreciated. Thanks Hi, Not able to test in xl2007 but from memory it should be the same as xl2003. For a line chart, select the x axis and open the format dialog. On the scale tab set Value (Y) axi...

Printing on 2 sides of a page?
How can I print a 2 page spreadsheet on the front and back of the same sheet of paper? Using Excel 2008. Thanks! It depends on whether your printer supports duplex printing. Check your printer's features to find out. If it does, the setting will be available in the Print dialog. You can also do a "manual duplex" job by printing page 1 (or 'Odd pages'), flipping the paper yourself, then print page 2 (or 'Even pages')... If it's only 2 pages that might be the simplest approach. -- HTH |:>) Bob Jones [MVP] Office:Mac "Rick"...

Match Debits and credits?
In my previous posts I was asking about counting unique invoice numbers and Purchase Order numbers (PO's). Well, now I have a new problem. I have to go through and evaluate the invoice numbers and po's and eliminate any matching debits and credits from my count. This may or may not affect including an invoice and po in my count. Many invoices can be included on one po. What type of formula do I use to match the debits and credits of an invoice number? MILTENYI BIOTEC INC 16-Aug-07 PO 0721868 778184 $455.00 MILTENYI BIOTEC INC 16-Aug-07 PO 0721868 778184 ($492.00) MILTENYI BIOTEC INC 1...

Draft Folder problem #2
I'm running outlook 2000. My draft folder is called draft1. Can't seem to rename it! There isn't a folder named draft so what's happening? Isn't a big problem, but I should be able to solve it and I can't. Help. Thanks in advance Peter You could try to start Outlook from the run line with the /resetfoldernames switch. Start-->Run outlook.exe /resetfoldernames Reference: http://office.microsoft.com/en-gb/assistance/HP010031101033.aspx "Peter Brown" <peter.jennifer@gmail.com> wrote in message news:TErag.449$TF.2645@news-1.opaltelecom.net... &...

Inventory #2
How can I import an excel spreadsheet as inventory into Money...? -- Marie You're talking about the home inventory? It's not possible without using a macro (i.e., there's no import function). IMO, it's not desirable either, as the information is not easily usable in Money. I keep my home inventory in an asset account, which has the huge benefit of being a "normal" Money account. If you need to import the inventory, there are Excel to QIF converters available from third parties. I use the following: http://www.bigredconsulting.com/ -- "Marie" <...

different domains
Hi there, How can I modify the OU in order to assign an especific domain to the users that belong to that OU? Suppose that I have two OUs, one called First and the 2nd one called second; and I have 10 peoples in each OU, but I have two domains, one colled domain1.com and the other one called domain2.com I want to create the 20 users, but once inside the OU the software should be able to assign the domain that belongs to tha OU. Is it possible? Tks a lot. tDL In the last exciting episode, "Administrator" <administrator@domoti-k.com> wrote: >Hi there, > >How can I ...

Type mismatch 04-02-10
Can anyone help? I am getting "Type mismatch (error 13)" when I try to call a method. The method is on a class module. The call is from a userform sub. The call: Dim DataSource as Leaf ... Set DataSource = new Leaf .... various properties of DataSource are used successfully ... .... then the problem call ... DataSource.Insert 1,"^" <<. msg occurs trying to execute this line .... The method: In class module "Leaf" ... Public Sub Insert (ipOrdinal, ipSubValue) Dim Ordinal Dim SubValue .... various statements .... call to another met...