Does Excel support onions?

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

[Anne Troy]

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 ]

[ercstric]

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 ]

[Aladin Akyurek]

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))))