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
418 Views

Similar Articles

[PageSpeed] 15

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:

How do I convert a column of cells with text emails to hyperlink to email ...
I have a column with text emails in it of the text form "person@website.com" I wish to convert the entire row to hyperlinks to email ... If I click on "HYPERLINK" and then select email it gives this hyperlink the text name of person@website.com and asks me to type in the email ... argh... -- Gerry I don't know if there's a quicker way, but one way is to double-click on each cell with the text in, then press enter, or click the green tick. Ian "G Vandevalk" <vdvalk@rogers.com> wrote in message news:5c6dnZdFavaCjnXcRVn-iA@rogers.com... >I h...

Multiple Instance of same form retrieving different data.
I have a form that shows Batch Jobs by name. Connected to this is a subform which shows unique Batch Job Name & Job Nbr combination records. The Batch Job Name and Job Nbr are the primary keys for the table being sourced by the subform. For Example, I have a form with Job Name of: Batch Job A and a sub form which Shows Batch Job A, 12 Steps & Batch Job A 16 Steps. The Job Nbr is hidden because its an internal number that ties back to source system. What I would like to do is give the user the ability click on the batch Job name in the sub form which will open form Batch...

Formatting text color in Outlook 2003
Is there a way to have Outlook 2003 remember my text color and use it in every email? I've changed my color several times but it still uses black as the automatic setting. I find this very frustrating since I prefer to use navy blue in all my emails but don't know how to have OL remember it Thanks! Sorry! I already found out how to accomplish this task. No need to reply. ...

Zip Code Lookup
Are there any add on programs that will automatically fill in the city state and country based on the postal code? You may want to check ZipAssist from Accountable Software at http://www.accountable.com/ZipAssist.asp?Product=ZipAssist Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com "AllenMc" wrote: > Are there any add on programs that will automatically fill in the city state > and country based on the postal code? ...

Quick Find search limitation
Hello, Is there any way to configure the Quick Find or edit your search string to search two different fields for any of the words in the search string. The request comes from a client who wishes to use the quick search to retreive leads based on a surname and city. We are in the scottish highlands and there are no shortage of macdonalds or macleods so they would like to search by the city simulataneously!! I have added city to the searchable fields, but the Quick Find can only successfully return leads when I submit a searchstring for Lastname or City (eg. enter "Macdonald"...

Search Directories/SubDirectories and Display in ListBox or MsgBox
I have modified the code below from a reply to a question in 2007 from Patrick Kirk. I am using MS Office Excel 2003. For my use I would prefer the results within 2 possible scenarios; 1) a ListBox so that I may choose just see or select a file that was found and proceed to work with it, such as open an XLS, if the file is an XLS file. 2) a MsgBox, but each file found must be displayed on separate lines within the MsgBox The difference for me too is that this IS NOT for a user form but rather a macro that can be assigned to a button. Suggestions? Sub findFile() ...

How to add Multiple Online Accounts from same online broker?
Hi All: I have two different accounts, with two different logins, etc., but with the same broker. When I try to add the 2nd account, for automatic online synchronization, the broker's name no longer appears in the list of available online brokers? It's as though Money figures there can only be one account per broker. How can I add the 2nd account? Thanks! Ev, Which version of Money are you using and what is the brokerage in question? Try any suggestions you get here on a copy of your Money file. Something that you might try is to stop using online services for that partic...

dealing with cell text limits
I'm helping a non-computer-savvy friend with an Excel spreadsheet that is used to manage a theater production. One page of this sheet is used for entering notes on the evening's show, e.g., problems with costumes, changes for lighting, etc. Sometimes these comments are empty or just a few words. Sometimes they are mini-essays, up to 30 or 40 lines of text. The cells containing the text need to resize appropriately, and need to (of course) display all the text they contain. She is running into big problems with Excel's behavior on cells that contain large amounts of text...

Text Import Wizard #5
When I try to import large text files, I get the error "Integer is invalid." when I set the "Start import at row value" to any values larger than 32767. Is this the limit for Excel? >>Is this the limit for Excel? It sure is. Goes why back: http://support.microsoft.com/default.aspx?scid=kb;en-us;119770 -- Jim "Pieter G" <Pieter G@discussions.microsoft.com> wrote in message news:F25ED78F-AD2F-4731-9A3D-FBF34BCDA61F@microsoft.com... | When I try to import large text files, I get the error "Integer is invalid." | when I set the "S...

how do i protect multiple worksheets
I have a workbook with 30 sheets where I would like to protect all the sheets at one time. I have followerd directions already posted and must be doing something wrong because I cannot protect all sheets. I guess I need vsome very basic help because I cannot make the codes work properly. I have entered as posted several times so I must be missing a step. Good evening Dennis If you don't want to get involved with VBA, I have a free add-in available to anyone requesting it that will (amongst other tasks) protect as many sheets as is necessary - with or without a password - at once. T...

Restrict a Text Box
Can you restrict a text box to only one line, my text box only shows one line! -- Thanks in advance for any help with this......Bob MS Access 2007 accdb Windows XP Home Edition Ver 5.1 Service Pack 3 Bob, In size or in characters? Not sure I understand... -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Bob Vance" <rjvance@ihug.co.nz> wrote in message news:ONinxuVxKHA.1692@TK2MSFTNGP04.phx.gbl... Can you re...

Any advice on multiple animations within a view?
Hi, I have an app where the main view (CView-derived) displays a background image and has multiple child windows running animations. I initially decided on a separate child window for every animation. Each animation window occupies anywhere from 1/8 to 1/4 of the view. Having separate windows simplified some things like management of timers, automatic clipping while painting the view's background (animations do not cover the entire view), etc. I'm now wondering if this is the most efficient way. I'm considering getting rid of child windows and doing animations within t...

storing text format in autocorrect
Hi, I'm trying to make bot excel and word more useful for myself. I run Office XP versions of both. I've got a macro that imports / exports the autocorrect list to an excel table and ideally I'd like to be able to comprise a list of autocorrect substitutions on excel and used that with both word _and_ excel. Is this possible at all? Let's just say I can't do the formatted text part with excel. At the moment I only know how to import / export the autocorrect list with Excel (using a macro I didn't make, credits to Dana DeLouis). How would I save my formatted text autoc...

V LOOKUP Question
Why is it when I am using the VLOOKUP function with spreadsheets dervived from different database sources at work(i.e. general ledger system, time reporting system) the VLOOKUP values are sometimes N/A when in fact there is an exact match. When I type the information over (exactly the same), the data is then found. Does this have anything to do with text formatting? Thanks! Alison Probably yes. Check for spaces or special characters in the cells. If the values are supposed to be numeric, they may not be recognized because in fact they are text. To cure that, format an empty cell a...

Collect data from spesific cell in multiple sheets
How can I collect data from cell A1 on sheet 01.01 thru 31.12 (Dates) to summary sheet? I've tried fill and copy formula with different use of ! and $ but I've not been able to solve this by my self. Thank you Hi, The correct syntax is =SUM(Sheet1:Sheet3!A1) Substitute sheet1 & sheet3 for the first/last sheets in the range you want to sum -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Espen Rostad" wrote: > How ...

Limit text box to any character except \, /, :, ?, <, >, !
Hello- I am currently working in a form which contains a text box that a user will use to create a job number and another text box for the job name. Later in the same form, the user will have the ability to create certain directories based upon this job number and another directory based upon the job name. I would like to put a function behind the after_update event of each event that will review this text box and make sure that the string entered does not contain any character that would cause a conflict when creating a directory, namely the following \ / : ? < > ! characters. What ...

Creating Multiple-Series Charts with as few steps as possible
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I routinely create charts with more than one series (e.g. several columns of data with x-axis labels in the first column and series names in the first row). <br><br>I can't get Excel to automatically recognize my first column of data as the x-axis and the first row of data as either the legend labels or y-axis (in 3D charts like a surface chart). The way I think it should work is: <br><br>1. I select the rectangle of data including x-axis labels in the first column and series names fo...

Multiple currencies
I'm getting ready to move overseas but will keep some accounts open in the US. Does anybody have any information about maintaining accounts in multiple currencies in the same money file? Does it work? Is Quicken better at this than MS MOney? Thanks Marcos In the UK version, we've had this for many years - no issues apart from some transfers (if you need to export/import as QIF) and occasional small issues with particular funds. Not sure on the US version, but suspect ift can handle it just as well. -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/def...

using outlook 2007 I can no longer paste jpg pictures into the text area
Good Day, I know that this is a simple questions and there has to be a setting in either Outlook or in office 2007 that will allow this to work. Outlook 2007 with SP1 plus all other updates (Office 2007 SP1) I just created a new system for a user and she was able to copy a jpeg into the text portion of an email, but now it becomes an attachment instead. Does anybody know were the setting are to allow jpeg to be inserted into the text portion of an email. Thanks Adam Raff Adam Raff, you wrote on Mon, 29 Sep 2008 10:03:32 -0400: > Does anybody know were the setting are to allow...

multiple conditions in COUNTIF (Excel 2000)
I want to count the number of occurences of a specific range in a list of numbers. I.e. the numbers range from 0 to 24 and I want to count the number of "hits" that are between 12 and 13. COUNTIF seems the obvious if want to keep it simple. But it seems COUNTIF can handle only one condition (">12" or "<13", not both), not allowing "AND" to be part of the criteria. Any suggestions? "Johbou" wrote: > I want to count the number of occurences of a specific range in a list of > numbers. I.e. the numbers range from 0 to 24 and...

bodlfacing text within larger text string
I have put together a test string with ="xyz"&text(celld4,"0.00")&"xyz" I would like to boldface the number (celld4) only. Can I add some kind of formating to the text( ) where I tell it a decimal format with 2 places ("0.00") as well or some other way? Thanks Hi no, not possible with formulas -- Regards Frank Kabel Frankfurt, Germany hovendic wrote: > I have put together a test string with > > ="xyz"&text(celld4,"0.00")&"xyz" > > I would like to boldface the number (celld4) onl...

Change text based on date
I want to change the text color on a report using the attached code but can't get it to wor not sure where I'm going wrong txtOrderDueDate is a long date? 12/08/2009 Private Sub Report_Load() If Me.txtOrderDueDate.Value <= Date + 2 Then Me.txtOrderDueDate.ForeColor = RGB(255, 0, 0) Else Me.txtOrderDueDate.ForeColor = RGB(0, 255, 0) End If End Sub Thanks! Jerry -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200912/1 jbair via AccessMonster.com wrote: >I want to change the text color on a report using t...

How to direct output from Word mail merge to multiple Word documents
Does anyone know if it is possible to send the results of a Word mail merge to multiple Word documents instead of combining everything into a single document? Thanks for any suggestions. Bill Choose the "form letters" output. --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx =20 <riddlewk@aeneas.net> wrote in message = news:1154627898.484999.26...

Multiple selections in a single cell
I have a spreadsheet which is used to produce reports. This contains a number of different sections and each section contains a list of common statements that can be selected from drop down lists in the report. Currently this is as up to three bullet points in three separate cells I would like to be able to combine 2 or 3 statements into a single sentence, presumably in a single cell, with minimal user involvement. How can I do this? If possible I'd prefer to avoid the use of macros which I don't have a good understanding of, but am prepared to learn if some one can give a co...

Selecting Multiple Bills for Entry
Hello all. I tried searching for any past posts on this, but the search tool is basically useless. Previous releases allowed the user to select multiple bills, then hit the "Enter in Register" button. Money would then cycle through them, one after the other. Starting with Money 2002 or 2003, that has gone away. How do we ask the developers to put that back in? It was a huge time saver when I sat down to write my bills out. Now I have to go one-by-one and make a lot of mouse clicks. Thanks for you time. In microsoft.public.money, longtimeuser wrote: > > >I tr...