Does Excel support onions?

G

Guest

Guest
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
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 :wink:
This message was edited by ercstric on 2002-03-01 23:37
This message was edited by ercstric on 2002-03-01 23:55
 
Upvote 0
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))))
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top