Lookup text with multiple search criteria

I know how to look up data and text using vlookup and hlookup, what I'
like to know is whether there is a way of looking up data based on mor
than 1 search criteria; for example

If I wanted to look up an item in a 4 column database, I'd us
something like this:

=VLOOKUP("Apples",A2:D10,4,false)

But that only looks for Apples. Say I wanted to lookup data based on 
subcategory of Apples, e.g. colour.

If it was a number, I could use SUMPRODUCT and (assuming named range
were in use) do it like this:

=SUMPRODUCT((Fruit="Apples)*(Colour="Red")*(Total))

But obviously SUMPRODUCT is no good if the data you want to return i
text.

Is there an equivalent text lookup function that will help me? Maybe a
INDEX/MATCH formula? I just can't get my head around it!!

Thanks for any help

--
Message posted from http://www.ExcelForum.com

0
6/16/2004 10:43:02 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
394 Views

Similar Articles

[PageSpeed] 17

http://www.cpearson.com/excel/lookups.htm
This site may help you.

"analyst >" wrote:

> I know how to look up data and text using vlookup and hlookup, what I'd
> like to know is whether there is a way of looking up data based on more
> than 1 search criteria; for example
> 
> If I wanted to look up an item in a 4 column database, I'd use
> something like this:
> 
> =VLOOKUP("Apples",A2:D10,4,false)
> 
> But that only looks for Apples. Say I wanted to lookup data based on a
> subcategory of Apples, e.g. colour.
> 
> If it was a number, I could use SUMPRODUCT and (assuming named ranges
> were in use) do it like this:
> 
> =SUMPRODUCT((Fruit="Apples)*(Colour="Red")*(Total))
> 
> But obviously SUMPRODUCT is no good if the data you want to return is
> text.
> 
> Is there an equivalent text lookup function that will help me? Maybe an
> INDEX/MATCH formula? I just can't get my head around it!!
> 
> Thanks for any help.
> 
> 
> ---
> Message posted from http://www.ExcelForum.com/
> 
> 
0
Crunched (2)
6/16/2004 5:42:01 PM
In article <analyst.17xw5o@excelforum-nospam.com>,
 analyst <<analyst.17xw5o@excelforum-nospam.com>> wrote:

> Is there an equivalent text lookup function that will help me? Maybe an
> INDEX/MATCH formula?

Yes there is!  Here's an example where we want to return the price/lb 
for yellow apples:

Fruit     Color     Price/lb
Apple     Red        0.48
Apple     Yellow    0.50
Grapes    White   0.60
Grapes    Red      0.59

=INDEX(C2:C5,MATCH(1,(A2:A5="Apple")*(B2:B5="Yellow"),0))

which must be entered using CTRL+SHIFT+ENTER.

Hope this helps!
0
domenic22 (716)
6/16/2004 7:27:13 PM
Brilliant! That works a treat!

Thanks

--
Message posted from http://www.ExcelForum.com

0
6/17/2004 10:31:22 AM
In article <analyst.17zqa8@excelforum-nospam.com>,
 analyst <<analyst.17zqa8@excelforum-nospam.com>> wrote:

> That works a treat!
> 
> Thanks!

You're welcome!
0
domenic22 (716)
6/17/2004 12:38:53 PM
Reply:

Similar Artilces:

Multiple Sessions of Outlook
Is it possible to run multiple Outlook sessions at the same time with different profiles? sid. No. -- Patricia Cardoza Outlook MVP www.cardozasolutions.com Author, Special Edition Using Microsoft Outlook 2003 ***Please post all replies to the newsgroups*** "Sid" <sidboswell@hotmail.com> wrote in message news:c6b901c3b9ed$98ef8e90$a601280a@phx.gbl... > Is it possible to run multiple Outlook sessions at the > same time with different profiles? > > sid. ...

SOP Document Lookup Error
Whenever we try using the document number lookup on the Sales Transaction Entry screen in GP8, we get the following message once we click the magnifying glass: Unhandled script exception: Index 0 of array 'Type Name' is out of range in script 'Document_Scroll SCROLL_FILL'. Script terminated. EXCEPTION_CLASS_SCRIPT_OUT_OF_RANGE SCRIPT_CMD_INDEX Does anyone have any ideas on how to clear this up? This pops up as soon as you click on the magnifying glass, then once you click ok, it pops up 2 more times. Do a query against table SOP10100 and look for any values in the SOP...

Deserialize an XML file with multiple namespaces...
Hopefully I can ask this question without regaling you kind people in too many gory details. Here's what I'm trying to do: - I have a local XML file with a .GPX extension which I downloaded from www.geocaching.com. - The file contains a bunch of elements called <WPT> - Within the WPT elements are a number of other elements <time>, <sym>, <groundspeak:name>... - The non-qualified elements come from a schema/namespace at http://www.topografix.com/GPX/ - The qualified elements come from a schema/namespace at http://www.groundspeak.com/cache - I ran xsd.exe with th...

problem with multiple SMTP domains in Exchange Server 2003
Hi there I have added a second SMTP domain to the Email Addresses (Policy) as follows: Recipients > Recipient Policies > Default Policy > Email Addresses (Policy) > New ... > SMTP Address > @xyz.com > (checked) This Exchange Organization is responsible for all mail delivery > to this address > Apply. When I do so, I no longer receive external emails on the first SMTP domain (@abc.com). Is this related to relay settings, or perhaps something else? Best regards Loane ...

Anyone found an easy way to search Case history?
Specifically the case notes, but I'll take what I can get. Use the advanced find, Look for 'Notes'/ 'Activity', define your search criteria: Regarding(Case) --> Case: Contains data. Result returned: all notes/ activity created in cases. If no criteria set but click on Find directly, every notes/activity created in MSCRM returned. "Jon" wrote: > Specifically the case notes, but I'll take what I can get. > > ...

Select multiple adjacent cells of multiple cells without selecting
Select multiple adjacent cells of multiple cells without selecting adjacent cells one by one. those cells looks like; |adjacent cells |cells| |some characters | A | |some characters | B | |some characters | A | |some characters | A | |some characters | B | I am trying to select adjacent cells of A cells without selecting adjacent cells one by one. Thank you for your help. Adjacent in this case, means? If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgr...

How to customize Outlook 2003, e.g. remove button text in toolbar?
I cannot find where to do that :( Thanks, Tien, Right click on the toolbar, select Customize, then right click on the button, select Default style. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Do Quyet Tien asked: | I cannot find where to do that :( | Thanks, | Tien, Thanks, but why is it easy like customize IE toolbars, just select option "No text labels...

Remove records from lookup
Not sure if this is possible but I would like to remove all records except the account records from the look up records dropdown menu of the appointment entity. The associated schema is regardingobjectid and the label is regarding. I see the other entities listed under relationships but there is no option to delete. Is there a way to create a new lookup and have it added to my appointment form? Thanks. There is no supported way to change this. The system is setup to allow you to set appointments that are related to specific records (Opportunities, Quotes, Cases, etc) and it will then ro...

entering multiple costs for an item
I am very new to Retail Management, and am trying to enter inventory, and create a purchase order. I can't figure out how to enter various costs for the same item from the same supplier. For example, when we purchase in lots of 50 pcs., the item cost is .50 per item (total cost of 25.00). However, if we purchase in lots of 100 pcs, the item cost is .47 per item (total cost of 47.00). We can also purchase the item in boxes of 10 (5.50 per box for a cost of .55 per item). How can I enter these costs so when I place a purchase order, the cost will be correct according to the quant...

Pull Data From Multiple Tables ????
Hi I will have 4 tables name "TblCostomers","TblVendors","TblAccounts", TblExpenses" Now i have a for name "FrmDrVouchers" that has a table "TblDrVouchers" in source. This form has two TxtBox Control name "TxtAccountNo" and "TxtAccountName" If User enters a Account No., It pulls the Account Name from Any One of these Table. I can do this if I have only one table. But tell me how can i do it while I have 4 tables for One Field of a table Thank you.. -- Message posted via AccessMonster.com http://...

Search folders Otlk-2003 don't work?
I create a new search folder in OUtlook folder with the criteria, "mail to and from certain people". And then I choose a person from the list. The email address for that person has always been the same. However the search folder only comes up with mail I have SENT to that person, not mail they have sent to me. Whats with that? thanks... jf ...

view multiple public calendars
We have seven public folders set up as calendar for room appointments. How can we view them side by side as shared calendars? The only way I can think of implementing this is to set up 7 user accounts and share their calendars. -- Posted via a free Usenet account from http://www.teranews.com version of outlook? if 2003 or 2007, add them to the public folder favorites and they'll be in the calendar list. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipst...

Charting depending on criteria & data series name as a column val
I want to chart some prices as I want to take a look at price trends. My problems, and I can not figure out how to solve them, are: 1. Is it possible to dynamically change the chart depending on certain criteria (product family & selected customers) 2. As the number of customers is variable and they are in one of the columns. Is it possible to plot a series (customer name), depending on the name of a column? 3. I want to chart the data based on the date, but just include the dates available, to prevent periods of time showing no change (e.g. I don't want monthly ticks for the x a...

how do i change or remove a signiture that is under outo text hea.
Someone please help i am using outlook 2003 and i am triyng to change a signiture that is under auto text. to get into it i open a message push insert then outo text then signiture. I want to change it. I have closed the untilted message gone to tools then options then mail format then signitures then remove all the signitures but the signiture under insert and outo text is still there See if this helps: http://office.microsoft.com/en-us/word/HP051860291033.aspx -- Bill R "nearly bald with frustration" <nearly bald with frustration@discussions.microsoft.com> wrote i...

Counting Texts
If cell A1 contains a paragraph of texts and if I want to count just letter "W"s (Upper or lower case), How can I achieve this? Example: A1 contains "How now brown cow" the formula should return "4". Thank you. Write a macro and use the VBA functions Instr(strName, "w") together with Split(strName, "w") and count the number of times that it finds "w" or "W" Chris "Keith" <Keith@discussions.microsoft.com> wrote in message news:CC60AA13-B569-4C55-BDC7-1B6295CDAFB9@microsoft.com... > If cell A1 co...

Emails missing text
We have about 80 users on our Exchange server. We have 2 users that have an issue with reading their email. It appears that when they open the email the right side of the email/text is gone. When I shadow their session I can see the entire email but they cannot. It is as if they right section of the text of the email was deleted. I'm sorry I'm not explaining this well. Any feedback is appreciated. Thanks ...

Export excel file to semicolon delimited text file
Hello all. We have the need to export a file from excel to a semicolon delimited text file. Is there a way to do this from Excel directly? If not, does anyone have any other suggestions? TIA for your help. If you change your regional settings to use the semicolon as your list separator, you can save as .csv and it should work. But this may affect other programs, too--since it's a windows setting. windows start button|settings|control panel|regional and language options| regional options tab|customize button near the bottom. On the other hand, if you don't want to fiddle with...

RMS--Multiple Loyalty Clubs
Does anyone make an add on to allow for multiple loyalty clubs per client? I would like to be able to set it up so that I can either print out a certificate every month and send the customer a voucher for their reward points for the particular club, or set it up so that certain levels of points trigger certificates for redemptions for certain SKUs of product vs. a flat cash credit to their account. contact me at secure payment systems for some really cool options regarding our loyalty card program that we are integrating with microsoft's retail management system right now. It's...

text size in reading pane
Where is the font selection / text size control for the Outlook 2003 reading pane (incoming emails)? I just bought a brand new dell with windows xp and all and I can't even increase the text size of incoming emails as I could with older versions of Outlook Express. I have been trying to figure this out for around 15 hours now and have come up empty. What kind of progress is this anyway???? A program rendered unable to accomplish such a simple practical thing like that!! Im very upset. My vision is poor. Unless I get an answer soon, I am considering a return of this unit to dell...

can't see text in any office files anymore
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel on my macbookpro: can't see text in any office files anymore (word, powerpoint, excel). only when using preview function from OSX. <br> what's wrong??? <br> do I have to re-install the all suite? any quick fix? ...

Viewing multiple excell spreadsheets on the task bar
Why is it that when I have 3 or more excell spreadsheets open they are not all listed on my task bar the only way I can get to them is by going to the window button and selecting the one I want to see, I want to be able to ALT TAB between them and they are not listing them for me in the task bar only the current one I have open? Are you using XP? Sounds like that feature of XP which groups several windows of one application into one spot on the task bar. There may be a way to shut this off in taskbar properties. Click on the task bar and select properties. "Megan" wrote: ...

Help Creating A Formula To Copy and Paste Text
Hi everyone. Thanks in advance for any help you may have. I need to create a formula that searches for a specific word in a column then cut the word and paste it to another column. Of course this would be simple if it was the only word in the column, but there is other text that I would like to remain in the orginal column.. Thanks again, Mary You have a reply at your other thread. Mary wrote: > > Hi everyone. Thanks in advance for any help you may have. > > I need to create a formula that searches for a specific word in a > column then cut the word and paste it to ...

Rich Text formatting in Access 2007
In Access 2007, is it possible to add rich text formatting to text in a memo field (or mixed formatting within any text field). I have just upgraded to the new version of Access, and it appears that this feature still has not been added. Is this correct? (If so, why?) If it is not possible to format individual words within a field in Access, , is there a simple, free add-in that will enable Access to do this kind of basic formatting, such as adding italic, bold, and underlining? Thanks very much for any information you may have. Bob Rich Text format for Memos in: Tables - Look in t...

Lookup returning incorrect results
Anybody have an idea why I keep getting incorrect lookup results. In sheet 1, I have a list of "Sales Codes" in col A. In sheet 2, I have a list of data that with column A for "Sales Codes" and column B for associated "Sales Reps". I want to insert a column in sheet 1 that contains lookup formulas to pull the correct sales rep for each sales code . Both sheets are sorted in ascending order by Sales code. My lookup formula in sheet 1, Lookup col B: =LOOKUP(A2,Sheet2!$A$2:$B$4,Sheet2!$B$2:$B$4) The returned results from the above lookup formula are incorrect....

Footnote text cannot be entered without mouse click
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Normally adding a footnote number automatically adds that number to the bottom of the page. A blinking text entry indicator normally allows me to enter the footnote text without any additional step. <br><br>However, today I wanted to add a footnote number after a word, and although the corresponding number correctly appears at the bottom of the page, and the blinking text entry indicator also appears ready to allow entering footnote text, yet when I try to type, nothing is entered. I must resort to ...