If then problems

[Guest]

I am having difficulty developing a formula that loos up data in two separate
columns and compares answers. Specifically, I need the formula to look at an
entered value, compare the value to an existing data list to see if it is in
the list. I then need the formula to search through a second data list to
find the corresponding value for the first enterable.

EX) If a user enters apple in the "favorite fruit" category, then I want
the formula to scroll through fruit list I have entered in a table on a
separate sheet (bananas, apples, oranges, grapes, pears, prunes,
strawberries). When it finds a value that matches the If statement, I then
want it to look at the point values I have for each of these fruits (bananas
= 5, apples = 4, oranges = 3, etc.) and pull the correct value out of a
separate, corresponding table. Specifically, the fruits are A12:A17 and the
points are C12:C17.

Help???????

 

[Guest]

If I understood your question correctly this should work:

=IF(ISNA(VLOOKUP(A20,Sheet2!A1:A4,1,FALSE)),"Not
Found",VLOOKUP(A20,A12:C16,3,FALSE))

Sheet2 Range A1:A4 is your list of items.

Range A12:C16 is your table of items with point values.

HTH

 

[Gord Dibben]

Favorite fruit typed into D1

In E1 enter =VLOOKUP(D1,$A$12:$C$17,3,FALSE)

Note: you can use Data Validation to make a drop-down list in D1. Then you
you can just click on the fruit rather than type it in.


Gord Dibben Excel MVP