Lookup Function Help

  • Thread starter Thread starter Dave Savitsky
  • Start date Start date
D

Dave Savitsky

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:

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.
 
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
 
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?
 
You could record a macro
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

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?
 
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 = 1 Then
' Only active if changing a value in the top row
If Target.Row = "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:=cel.Value,
RefersToR1C1:="=R2C" & cel.Column & ":R26C" & cel.Column
End If
Next cel
End If
End If
End Sub

Thanks again for the help.
 
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
 
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 said:
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
Click to expand...
 
That's nice, Shane. I have never thought of using INDEX with a missing
argument to mean the entire column!
best wishes
 
Back
Top