Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Does Excel support onions?

  1. #1
    Guest

    Default

    Dear MrExcel,
    This is the situation:
    Three columns- A, B, and C

    A B C
    p 1 potato
    q 1 tomato
    r 1 ketchup
    p 2 cheese
    q 2 onions
    r 2 garlic
    p 3 peper
    q 3 salt
    r 3 eggs
    . . .
    . . .
    . . .

    What to write so that D1 shows the item from the column C that has the same row number as the value q in the column A and as the value 2 in the column B? (that is onions here)

    Thanks,
    KNELE

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you can, please rename your question. The title is cute, but not helpful to anyone searching the database.

    And please ask your question only once.

    These *rules* are for all tech sites and a means of being considerate, not just here at MrExcel. Thanks!



    It sounds like you need a Vlookup, but you're not specifying WHAT you want to look up--i.e., do you want to type a q in D1 and have some other cell bring up the other value(s)? Why don't you do a search? I've explained vlookups at least once myself, but I think a couple of times.

    _________________
    TheWordExpert

    [ This Message was edited by: Dreamboat on 2002-03-01 21:45 ]

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    16
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    probably an easier way ... but anyway here goes

    say you add a column between your "a" & "b" (so now your old colmn "b" is "c") with this formula
    =IF(A1="p",1,0)+IF(A1="q",2,0)+IF(A1="r",3,0)
    then in colum "e" this formula
    =IF(B1=C1,D1,0)
    copy and paste these through the range, voila
    onions supported

    looks like this
    A B C D E
    p 1 1 potato potato
    q 2 1 tomato 0
    r 3 1 ketchup 0
    p 1 2 cheese 0
    q 2 2 onions onions
    r 3 2 garlic 0
    p 1 3 peper 0
    q 2 3 salt 0
    r 3 3 eggs eggs

    if you want, replace the 0 with "", to return a blank cell

    cheers,
    eric



    [ This Message was edited by: ercstric on 2002-03-01 23:37 ]

    [ This Message was edited by: ercstric on 2002-03-01 23:55 ]

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,633
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-01 17:15, Anonymous wrote:
    Dear MrExcel,
    This is the situation:
    Three columns- A, B, and C

    A B C
    p 1 potato
    q 1 tomato
    r 1 ketchup
    p 2 cheese
    q 2 onions
    r 2 garlic
    p 3 peper
    q 3 salt
    r 3 eggs
    . . .
    . . .
    . . .

    What to write so that D1 shows the item from the column C that has the same row number as the value q in the column A and as the value 2 in the column B? (that is onions here)

    Thanks,
    KNELE
    If q and 2 are the "keywords" for which you want to retrieve the associated value, you can do what follows.

    Assuming that A2:C10 houses the sample data provided and no duplicate rows (rows that are exactly eq) occur in the said range,

    in E1 enter: q

    in E2 enter: 2

    in D1 enter:

    =INDEX(C2:C10,SUMPRODUCT((MATCH(E1&"@"&E2,A2:A10&"@"&B2:B10,0))))

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •