Lookup Function Help

I need a little help constructing a lookup function.  See the table
below:
http://pics.livejournal.com/lonewolf_csu/pic/0002c0bc

I'm trying to get the TRUE/FALSE values in G based on whether or not
the value of F is in the column with the header ($1) that matches the
value in E.

Obviously the example is hard data with no functions.

Thanks in advance for the help.

--
Dave Savitsky

0
10/21/2008 7:02:50 PM
excel 39879 articles. 2 followers. Follow

8 Replies
488 Views

Similar Articles

[PageSpeed] 5

Dave:

Rearrange your table with "Veggies-fruits-either" from 3 columns into a 2 
columns list with item name and category, E.G.:

Item_Name    Category
Apple              Fruits
Carrot            Veggies
Orange            Either
....                    .....

And create from your list #2 a new column with the lookup function: 
(assuming it will be on "H")

=if(iserror(vlookup(f1,a1:b20,1,false)),"Not on the 
list",text(vlookup(f1,a1:b20,2,false),"General")=text(e1,"general"))

where: a1:b20 is the list of the "veggies-fruits-either" (VFE), you can use 
$a$1:$b$20 to have this area fixed when copying the formula to the rows 
below, but remember to extent this selection when more items are added to 
the "VFE" list.

This formula evaluates wheter or not the item exist on your list, this 
avoids the possibility of somebody introducing a different item like "Beet" 
on the list and retrieving an error message. After this evaluation it 
compares the text values of the category of the existent item to the column 
"E" in the same row the item is and return if equal: "True" if not, "False".
For this function to work the name of the items and group should have been 
written in the same way and no additional spaces as these count on the 
comparison.

Good luck on your project, let me know if it works well for your needs.



"Dave Savitsky" <lonewolfcsu@gmail.com> wrote in message 
news:659addcd-b2b9-42b3-ab42-a77fb6022a64@w39g2000prb.googlegroups.com...
>I need a little help constructing a lookup function.  See the table
> below:
> http://pics.livejournal.com/lonewolf_csu/pic/0002c0bc
>
> I'm trying to get the TRUE/FALSE values in G based on whether or not
> the value of F is in the column with the header ($1) that matches the
> value in E.
>
> Obviously the example is hard data with no functions.
>
> Thanks in advance for the help.
>
> --
> Dave Savitsky
> 


0
Leo
10/21/2008 8:37:57 PM
Select A1:C100 (ie down to the last row); use Insert | Name and specify top 
row
Now A2:A100 is called Fruits, B2:B100 is called Veggies and C2:C100 is 
Either
In G1 enter =COUNTIF(INDIRECT(E1),F1)=1
Copy down the column to get TRUE/FALSE
best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Dave Savitsky" <lonewolfcsu@gmail.com> wrote in message 
news:659addcd-b2b9-42b3-ab42-a77fb6022a64@w39g2000prb.googlegroups.com...
>I need a little help constructing a lookup function.  See the table
> below:
> http://pics.livejournal.com/lonewolf_csu/pic/0002c0bc
>
> I'm trying to get the TRUE/FALSE values in G based on whether or not
> the value of F is in the column with the header ($1) that matches the
> value in E.
>
> Obviously the example is hard data with no functions.
>
> Thanks in advance for the help.
>
> --
> Dave Savitsky
> 


0
bliengme5824 (3040)
10/21/2008 8:56:38 PM
Insert | Name | Create ... (you left off the last part)

Thanks for the input, that's exactly what I was looking for.

Is there a way to automate the naming of the columns?  For example,
let's say I added "Other" to the top of column D and included "Rock"
in that list.  Will I have to manually recreate the names each time I
do something like this, or can I automate it/set it up in advance?

--
Dave Savitsky

On Oct 21, 2:56=A0pm, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:
> Select A1:C100 (ie down to the last row); use Insert | Name and specify t=
op
> row
> Now A2:A100 is called Fruits, B2:B100 is called Veggies and C2:C100 is
> Either
> In G1 enter =3DCOUNTIF(INDIRECT(E1),F1)=3D1
> Copy down the column to get TRUE/FALSE
> best wishes
> --
> Bernard V Liengme
> Microsoft Excel MVPhttp://people.stfx.ca/bliengme
> remove caps from email
>
> "Dave Savitsky" <lonewolf...@gmail.com> wrote in message
>
0
10/21/2008 9:19:44 PM
You could record a macro
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Dave Savitsky" <lonewolfcsu@gmail.com> wrote in message 
news:f29827b2-5ced-4447-908b-8f90830f27d3@1g2000prd.googlegroups.com...
Insert | Name | Create ... (you left off the last part)

Thanks for the input, that's exactly what I was looking for.

Is there a way to automate the naming of the columns?  For example,
let's say I added "Other" to the top of column D and included "Rock"
in that list.  Will I have to manually recreate the names each time I
do something like this, or can I automate it/set it up in advance?

--
Dave Savitsky

On Oct 21, 2:56 pm, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:
> Select A1:C100 (ie down to the last row); use Insert | Name and specify 
> top
> row
> Now A2:A100 is called Fruits, B2:B100 is called Veggies and C2:C100 is
> Either
> In G1 enter =COUNTIF(INDIRECT(E1),F1)=1
> Copy down the column to get TRUE/FALSE
> best wishes
> --
> Bernard V Liengme
> Microsoft Excel MVPhttp://people.stfx.ca/bliengme
> remove caps from email
>
> "Dave Savitsky" <lonewolf...@gmail.com> wrote in message
> 


0
bliengme5824 (3040)
10/21/2008 9:28:17 PM
Came up with this while waiting.  Not the cleanest (I haven't done any
VB work in Excel previously), but it seems to work.

Private Sub Worksheet_Change(ByVal Target As Range)
' Make sure we're not changing a range of cells (causes errors)
    If Target.Cells.Count =3D 1 Then
    ' Only active if changing a value in the top row
        If Target.Row =3D "1" Then
        ' Delete all ranges (can't figure out how to retrieve old name
to just delete that range)
            For Each nName In ActiveWorkbook.Names
                nName.Delete
            Next nName
            ' Crate ranges for all top row values (out to Z...)
            For Each cel In Range("$A$1", "$Z$1")
                If cel.Value <> "" Then
                    ActiveWorkbook.Names.Add Name:=3Dcel.Value,
RefersToR1C1:=3D"=3DR2C" & cel.Column & ":R26C" & cel.Column
                End If
            Next cel
        End If
    End If
End Sub

Thanks again for the help.

On Oct 21, 3:28=A0pm, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:
> You could record a macro
> --
> Bernard V Liengme
> Microsoft Excel MVPhttp://people.stfx.ca/bliengme
> remove caps from email
>
0
10/21/2008 10:56:12 PM
Hi,

If you don't want to use range names then here is a solution:
=COUNTIF(INDEX($A$2:$C$5,,MATCH(E1,$A$1:$C$1,0)),F1)>0

-- 
Thanks,
Shane Devenshire


"Dave Savitsky" wrote:

> I need a little help constructing a lookup function.  See the table
> below:
> http://pics.livejournal.com/lonewolf_csu/pic/0002c0bc
> 
> I'm trying to get the TRUE/FALSE values in G based on whether or not
> the value of F is in the column with the header ($1) that matches the
> value in E.
> 
> Obviously the example is hard data with no functions.
> 
> Thanks in advance for the help.
> 
> --
> Dave Savitsky
> 
> 
0
10/22/2008 1:30:01 AM
Hi,

Here is another formula not requiring range names
=SUM(--((E1=A$1:C$1)*(F1=$A$2:$C$5)>0))
Array entered.

-- 
Thanks,
Shane Devenshire


"Bernard Liengme" wrote:

> Select A1:C100 (ie down to the last row); use Insert | Name and specify top 
> row
> Now A2:A100 is called Fruits, B2:B100 is called Veggies and C2:C100 is 
> Either
> In G1 enter =COUNTIF(INDIRECT(E1),F1)=1
> Copy down the column to get TRUE/FALSE
> best wishes
> -- 
> Bernard V Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> remove caps from email
> 
> "Dave Savitsky" <lonewolfcsu@gmail.com> wrote in message 
> news:659addcd-b2b9-42b3-ab42-a77fb6022a64@w39g2000prb.googlegroups.com...
> >I need a little help constructing a lookup function.  See the table
> > below:
> > http://pics.livejournal.com/lonewolf_csu/pic/0002c0bc
> >
> > I'm trying to get the TRUE/FALSE values in G based on whether or not
> > the value of F is in the column with the header ($1) that matches the
> > value in E.
> >
> > Obviously the example is hard data with no functions.
> >
> > Thanks in advance for the help.
> >
> > --
> > Dave Savitsky
> > 
> 
> 
> 
0
10/22/2008 2:16:01 AM
That's nice, Shane. I have never thought of using INDEX with a missing 
argument to mean the entire column!
best wishes
-- 
Bernard

"ShaneDevenshire" <ShaneDevenshire@discussions.microsoft.com> wrote in 
message news:B27330D3-4B94-4AF1-84B6-0C02EA6F2C29@microsoft.com...
> Hi,
>
> If you don't want to use range names then here is a solution:
> =COUNTIF(INDEX($A$2:$C$5,,MATCH(E1,$A$1:$C$1,0)),F1)>0
>
> -- 
> Thanks,
> Shane Devenshire
>
>
> "Dave Savitsky" wrote:
>
>> I need a little help constructing a lookup function.  See the table
>> below:
>> http://pics.livejournal.com/lonewolf_csu/pic/0002c0bc
>>
>> I'm trying to get the TRUE/FALSE values in G based on whether or not
>> the value of F is in the column with the header ($1) that matches the
>> value in E.
>>
>> Obviously the example is hard data with no functions.
>>
>> Thanks in advance for the help.
>>
>> --
>> Dave Savitsky
>>
>> 


0
bliengme5824 (3040)
10/22/2008 2:07:07 PM
Reply:

Similar Artilces:

I need help
What do I do to create a form to be filled out with lines? I made one and when I went to fill it out the words over took the lines and caused them to overlap and make it look messy. How do I create the form so I can type the words on top of the lines and not in the lines? A table is your best bet. or Put the lines on the Master Page. (ctrl + m). Opening the baseline guides could be helpful. (view menu) Word has this function with forms, you might want to think about doing forms with Word. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.mi...

Table relationships and lookups
Hi guys, I may be a little over my head, I've had some experience in creating simple access db's. however this one will be extremely complicated as far as I can tell. Some backround info - i've got an excel spreadsheet currently that i would like to convert to Access. The spreadsheet does multiple lookups and calucations. This is for a Soccer club that i run to maintain roster information, dollars, scheduling and stats. I'm currently working on the scheduling pience. Here's what I have so far. tables. Club - Lists the teams in the club, home field name and ...

Duplicate emails -- HELP!
Good day. I am running Exchange 5.5 on a WIN2K Server. I have Oulook 2003 as a client and one of my users is running Outlook XP. We are both having the same problem... When you receive email from certain individuals, we receive them in duplicate. I've looked through all the whitepages and couldn't find anything. I appreciate any help you can offer me. Thank you in advance. Roger Settle RSettle@klfinancialgroup.com Exchange Server 5.5 is not compatible with Outlook 2003. Thats the reason why we use Outlook 2k/2002 in our company because we have exchange 5.5 >----...

Question about IF function
I would like to have every cell in column A that has value of "0.00" be changed to "NO" and every cell that is > 0.00 to have number values changed to "YES". What would the formula be? Thanks in advance. Not sure why you're thinking IF() function. If you just want to display "YES" or "NO", choose Format/Cells/Number/Custom and enter: "YES";;"NO";@ Otherwise In article <12750bd4-e150-4c25-92ca-2b06e540f525@t3g2000yqa.googlegroups.com>, excel_21 <lin.jeff.21@gmail.com> wrote: > I would like...

OWA problem, Please help is really urgent.
The page cannot be found The page you are looking for might have been removed, had its name changed, or is temporarily unavailable. -------------------------------------------------------------------------- Please try the following: a.. If you typed the page address in the Address bar, make sure that it is spelled correctly. b.. Open the server home page, and then look for links to the information you want. c.. Click the back button to try another link. HTTP 404 - File not found Internet Information Services ---------------------------...

Invoice lookup by paid check number
I frequently have vendors call me asking for information about what invoices were paid by a check number. Is ther an easy way to look this information up? -- Rodger You could go to Inquiry>Purchasing>Transactions by Document, put your check number in the 'from' and 'to' fields. Once the document is displayed in the scrolling window zoom back on the 'Unapplied Amount' field. Viola! the documents paid by the selected payment are listed. Unfortunately there isn't a print icon on this inquiry window, but I think it's the information you wanted. &quo...

Office Assistant/Help Topics
In excel if I choose the office assistant and type a question to get help, topics come up. But, when I choose a help topic a blank white box just pops up. ...

Lookup
Q103 Q102 Q202 Q302 Q402 Q103 Q203 How can I lookup the Q103 in the row above and then have it pull the number to the right one cell (Q203)? thanks If I understand correctly =INDEX(A2:F2,MATCH(A1,A2:F2,0)+1) where q103 is in a1 and q102-q203 is in a2-f2 Lance >-----Original Message----- >Q103 > > >Q102 Q202 Q302 Q402 Q103 Q203 > > >How can I lookup the Q103 in the row above and then have >it pull the number to the right one cell (Q203)? thanks >. > matt wrote: > Q103 > > > Q102 Q202 Q302 Q402 Q...

Need offset function help, I think
I have 10 products rows with a cost in column BW of rows 21 thru 30, i.e., cells BW21 down to BW30. Those 10 products have the # of units sold each month, for 22 months, shown in rows 41 thru 50, in columns E thru Z, so column E is month #1 ... col Z is month #22. The total range is cells E40 thru Z40. There is a cost factor vector that has six factors in row 70, cells G70 thru L70. These are to be used =91for all time periods=92 and for all products. Any time there is a sale of a product in a certain month, I want to enter six months of cost associated with producing the item...

Select range for function in a cell
Hi, is it possible to select create a formula in a cell. such as =SUM( and have the cursor be between the () so the user can select th appopriate range to enter? Or would this just be done separately. (Since one would need to know the range and WHERE to put the formul in..ie. what cell) similar to if you hit the autosum button but ther are no numbers above or the left, it simply waits for the user t select a range and then hits enter. THanks! -- Message posted from http://www.ExcelForum.com Hi if I understood you correctly: No -- Regards Frank Kabel Frankfurt, Germany > Hi, is it p...

Help with LOOKUP function
This function is in a workbook with 2 sheets. It _almost_ works perfectly. These "C" columns in two different sheets '2005-2006'!C:C,'2004-2005'!C:C, contain names of people. The D column in one of the sheets - '2004-2005'!D:D - contains a date associated with the person's name from the C column of 2004-2005 sheet. This formula is in the "D" column of Sheet 2005-2006. =LOOKUP('2005-2006'!C:C,'2004-2005'!C:C,'2004-2005'!D:D) The concept is for the formula to lookup the value (person's name) in column C of 2005-2006 a...

Microsoft Office Outlook has stopped working
Outlook 2007 has been installed for all of 6 days. Today it stopped responding. I can no longer run Outlook 2007. Every time I open it I get "Microsoft Office Outlook has stopped working" I do the Check online for a solution and close the program, and the program just closes. I have run Microsoft Office Diagnostics and no errors. Scanpst and no problems. I am running Vista Business and Office 2007. I hate I am going to have to go back to XP Pro and Office 2003, but this is unbearable. My problem is I copied all my emails to Outlook 2007 so it is no longer a 200...

A Lookup function does not work
Hi, This is my first posting. I am using Exel 2000. I have 2 separate spreadsheets that have some similar columns but not all of the data in the similar columns is the same. What I want to do is take column A in spreadsheet#1 and find this same value in Column B in Spreadsheet#2 and then insert into column 3 in spreadsheet #1 a value from a different column in spreasheet #2 that corresponds to the row in which the value was looked up in Column B in spreadsheet#2. What I am doing is comparing 2 different inventory files that have stock codes in columns and quantities in another column, but n...

HELP! Worksheet Problem
Hi I have a workbook containing 6 worksheets being Nov 01, Feb 02, May 02, Aug 02, Nov 02 and Jan 03. Each worksheet contains details of loans on these dates to clients. Each client has a unique ID number and is in its own row with columns A-I being ID, Prefix, First Name through to post code etc and column J being the loan amount. The first worksheet (Nov 01) contains 76 clients, the second (Feb 02) contains 189, (May 02) = 297, Aug 02 = 345, Nov 02 = 342 and the last (Jan 03) contains 350 clients. I need a way to import all the details of the clients from each worksheet to a ...

SumIf Function #3
I'm trying to use the SumIf Function (as I understand its use) and I keep running into the same problems -- in many cases I have more than one criterion for addind a specific cell. Is there a way to modify SumIf to allow for multiple criteria? I try to do it in the insert function box and it gets spit back out at me. Any ideas? Jay Jay look at SUMPRODUCT Look at the Help and search the Archives for examples Regards Trevor "jayceejay" <jayceejay@discussions.microsoft.com> wrote in message news:39CB49F6-4459-4A4B-8856-E3E8BE615FFB@microsoft.com... > I'm...

Help please
I'm using Money 2002 Version and in my checking account, i clicked "hide spending thermometer" and i cant figure out how to get it back. anyone know? Tools|Options|Feedback|Personalized Feedback in the Account Register and Portfolio|Options... ??? "FA" <Sylverphire@hotmail.com> wrote in message news:058c01c36927$0e214bd0$a401280a@phx.gbl... > > I'm using Money 2002 Version and in my checking account, > i clicked "hide spending thermometer" and i cant figure > out how to get it back. anyone know? ...

CRM Functionality does not appear in Outlook
Hi all... Hope someone can help here - I am a non-technie looking after the install of CRM for a small sales organisation. I have succesfully installed the outlook client on all laptops but on 1 desktop I am getting a problem. I have installed all pre-requisites and passed all pre-install checks, and the installation appears to go smoothly. However, the additional functionality does not appear in outlook. No error codes are generated so I can't be more specific than this I'm afraid, any ideas would be greatly appreciated. Cheers Holmesy in outlook go to tools options..then get t...

return cell reference in a table based upon given lookup criteria
Is there a way to return the cell reference, or column/row coordinates, of a cell within an array or table by providing lookup criteria? Perhaps something like this: For a table of value in A1:E10 F1: (the value to find) G1: =ADDRESS(MAX((A1:E10=F1)*ROW(A1:E10)),MAX((A1:E10=F1)*COLUMN(A1:E10))) Note: Commit that array formula by holding down the [Ctrl][Shift] keys and press [Enter]. That formula returns the address of the 1st cell containing the value in F1, or #VALUE! if there is no match. Am I on the right track here? *********** Regards, Ron XL2002, WinXP-Pro "Travis" ...

POP email multiple duplicate downloads HELP!
Hi, ISP is SBC Global. I just installed Outlook 2003 yesterday and it keeps downloading ALL the mail I have in the SBC web inbox, even though I"ve marked them as "read" online, multiple times. Duplicate finder [bought and installed] doesn't recognize most of them as dupes. I've done "detect & repair," [got error 1311 source file not found; C:\msocache\allusers\90000409-60000-11D3-8CFE-0150048383C9\ZS561.CAB. Couldn't find that in my installation disk, either.] SBC says they do not support Outlook 2003 -- "it's too new." Superviso...

IF Function Problem
Hello All, I am using Office XP and have the following problem I have a worksheet which uses lookup to extract names from range name Codes as follows Col O=IF(ISNA(VLOOKUP(D2,CODES,6,FALSE)),"",(VLOOKUP(D2,CODES,6,FALSE))) I wish to add another IF condition to the above formula to test that: If Col D2 = EGSP AND Col A = date greater than or equal to 15-Nov-2005 then it should display the name as 'Jack' otherwise 'James'. All other values should remain unchanged Thanks a lot Rashid =IF(D2="EGSP",IF(A2>=--"2005-11-15","Jack",&q...

Conditional Formatting in Excel Help Please.....
Newbie to VBA I need to use Conditional Formatting to format a range of cells to have Wheat background and Red text (Bold) if a text string is contained in each cell. For example, all cells in a range that contain the text string "LLC" or "Inc" or "Corp" or "Corporation" or "Company" , etc. If cell E2 contains Acme, LLC I need to give that cell (E2) the condition formatting If cell E3 contains John Brown That cell does not get the condition formatting If cell E4 contains Joe's Pool Hall, Inc Cell E4 gets the condition formatting Thanks ...

Consistent function of Utilities logon screen when applying hotfix
When our users attempt to install a hotfix roll-up, when they are logging into Dynamic Utilities for the first time, the user interface is very touchy. What I mean is that they must click the username field and then type. Then, they must use the mouse again to click the password field and type. Then, they must click the OK button. If they try to use backspace, enter key, tab key, etc., extra characters are inserted. The only way that they can "edit" their username and password information during logon is to use the mouse to highlight and the type over the information. ...

Advanced Lookups
Is there any way to make an advanced lookup the default lookup? so you don't have to always choose that option when doing a lookup? Thanks for any help. Tracey D Advanced lookups ARE the default unless you've done something to make it now so. There isn't any way to "choose" the option when doing a lookup that I know of unless you have some type of customization (easy to do) that would give the user an option. patrick dev support -- This posting is provided "AS IS" with no warranties, and confers no rights. "Tracey D" <...

How to call a non static function from a static function
Hi I need to go to a non static function from a static function?can anybody suggest me how to do it?any kind of help will be greatly appreciated. You need to pass the object whose function you want to call. static void s_foo( A* a ); class A{ public: A(); ~A(); void funcA(); }; main() { ... A a; s_foo(&a); } static void s_foo( A* a ) { a->funcA(); } -Seetharam ...

VLookUp Help!
I am trying to put together a pricing wizard for my company that would allow reps and resellers to easily look up the prices of our products. I originally designed a pivot table with drop down lists but this proved to be to difficult for most people to use. I though instead I could use VLookup to accomplish the same thing. The problem is we price our products by quantity. 5 to 25 being one price 26 to 50 being another and so on. Because of the ranges of pricing I can not figure a way, using VLookup to actually look up a price. Here is an example of the pricing: # Part # of Licen...