Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Excel IF function for a logical question containing a range of named values?

Options
  • 26-11-2010 11:49am
    #1
    ailsybird
    Registered Users Posts: 17 ✭


    Hi,
    I've got a huge database (about 37000 rows, 25 columns) I've been working on for the last few weeks, but am stuck on how to do the following -

    Each row corresponds to an individual client record. I need to group the rows by the data in column D. Data in column D is logged as specific items (for example apples, pears, cabbage, milk). However I need to group this by one of four types of purchase (vegetables, condiments, fruit, other).

    In another sheet I have all the groupings ie. apples, pears etc ('Sheet2'!A1:A20) are fruit, cabbage, carrots etc ('Sheet2'!A21:A40) and so on.

    I tried to use a nested IF function eg
    IF(D1='Sheet2'!A1:A20,"fruit",IF(D1='Sheet2'!A21:A40,"vegetable",IF(D1='Sheet2'!A41:A60,"condiment",IF(D1='Sheet2'!A61:A80,"other"))))
    but it gives only #VALUE!

    I checked that it wasn't my annotation by inserting rather exact cell references (eg 'Sheet2'!A16) and this works fine, it's only when you're asking it to look at a range of data that the function fails.

    Any ideas?
    Thanks
    ailín
    Tagged:


Comments

  • Options
    #2
    bnt
    Registered Users Posts: 12,967 ✭✭✭✭bnt


    First of all, you're right that the function is failing because you're trying to do an IF comparison on a range, and I'm pretty sure that it needs to be on a single cell or value. The best way I know to do what you want is to use a VLOOKUP function.

    In your example, since you have 'Sheet2'!A1:A20 with the fruits, you can put "fruit" in each cell in 'Sheet2'!B1:B20, and then the same for the vegetables in 'Sheet2'!B21:B40, and so on down. Then a function like this will return "fruit" or "vegetable" etc.
    =VLOOKUP(D1,Sheet2!$A$1:$B$80,2,FALSE)

    Notes: the "FALSE" means "only exact matches" i.e. it has to match exactly or it will return #N/A. If you leave that out, it will return approximate matches, which you might not want - your choice. The $ signs will ensure that the references to the table don't change if you copy that cell down the table.

    PS: is a tomato a fruit or a vegetable?

    From out there on the moon, international politics look so petty. You want to grab a politician by the scruff of the neck and drag him a quarter of a million miles out and say, ‘Look at that, you son of a bitch’.

    — Edgar Mitchell, Apollo 14 Astronaut



  • Options
    #3
    rossc007
    Registered Users Posts: 627 ✭✭✭rossc007


    Hi Ailin,

    Not sure why your using Excel at all, cant you just run a query to do the grouping for you?

    Do the Specific Items(apples, pears etc) have a relational link to the purchase type(vegetables, fruit etc)?


  • Options
    #4
    bnt
    Registered Users Posts: 12,967 ✭✭✭✭bnt


    The OP didn't say that the data was extracted from another database, from reading the question I thought that Excel was the database. Well, Excel works just fine as a "flat file" database, if you ask me. It has Grouping facilities built in (Data menu/tab), it's just that the criteria for grouping need to be in the table before you can do that, and solving this problem can fill that gap. ;)

    From out there on the moon, international politics look so petty. You want to grab a politician by the scruff of the neck and drag him a quarter of a million miles out and say, ‘Look at that, you son of a bitch’.

    — Edgar Mitchell, Apollo 14 Astronaut



  • Options
    #5
    ailsybird
    Registered Users Posts: 17 ✭ailsybird


    Thanks bnt, VLOOKUP is exactly what I was looking for. Works absolutely beautifully!

    rossc007, yes the datasheet has been in Excel format all along, I'm kinda stuck working with it for the forseeable future.

    That old tomato issue...ambiguous vegetability-it goes in salads and on pizza, but yet classed as a fruit. It's a metro-vegetable. :)


Advertisement