reference 2 different cell and the result will com from the third cell

Jeorge · Jun 2, 2018

Need your help guys,
I have this example regarding my concern of giving me the figure from cell C (Quantity) from reference of cell A (Product) and cell B (Company). I am trying to figure out how to get the value of Banana Del Monte as reference value and will give me the 15,000 to my separate worksheet. Tried this:
=IF(AND(A2=”Banana”, B2=”Del Monte”), C2,0)
That gives me the result of 15,000 from where I place the formula but since every day I need to extract the order this placed the Banana and Del Monte in a different cell like the one showing in Figure B.
Tried using =IF(AND(A:A=”Banana”, B:B=”Del Monte”), C:C,0) but I think that is totally not right (Haha)

Assuming that we have this imaginary excel lines...

Figure A
A B C

[TABLE="width: 211"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]1 Apple
[/TD]
[TD] Fuji
[/TD]
[TD="align: right"]10000[/TD]
[/TR]
[TR]
[TD]2 Banana
[/TD]
[TD] Del Monte
[/TD]
[TD="align: right"]15000[/TD]
[/TR]
[TR]
[TD]3 Orange
[/TD]
[TD] Sunkist
[/TD]
[TD="align: right"]12000[/TD]
[/TR]
[TR]
[TD]4 Pineapple
[/TD]
[TD] Dole
[/TD]
[TD="align: right"]11000[/TD]
[/TR]
[TR]
[TD]5Strawberry
[/TD]
[TD] Baguio
[/TD]
[TD="align: right"]7000[/TD]
[/TR]
[TR]
[TD]6 Kiwi
[/TD]
[TD] Aussie
[/TD]
[TD="align: right"]12000[/TD]
[/TR]
</tbody>[/TABLE]

Figure B
A B C

[TABLE="width: 209"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]1Strawberry
[/TD]
[TD]Baguio
[/TD]
[TD="align: right"]7,000[/TD]
[/TR]
[TR]
[TD]2Pineapple
[/TD]
[TD]Dole[/TD]
[TD="align: right"]11,000[/TD]
[/TR]
[TR]
[TD]3Orange
[/TD]
[TD]Sunkist[/TD]
[TD="align: right"]12,000[/TD]
[/TR]
[TR]
[TD]4 Kiwi
[/TD]
[TD]Aussie[/TD]
[TD="align: right"]12,000[/TD]
[/TR]
[TR]
[TD]5 Banana
[/TD]
[TD]Del Monte[/TD]
[TD="align: right"]15,000[/TD]
[/TR]
[TR]
[TD]6 Apple
[/TD]
[TD]Fuji [/TD]
[TD="align: right"]10,000[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 209"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Peter_SSs · Jun 2, 2018

See if something like this might help you.

Excel Workbook

A

B

C

D

E

F

1

Fruit

Type

Quantity

2

Apple

Fuji

10000

Fruit

Banana

3

Banana

Del Monte

15000

Type

Del Monte

4

Orange

Sunkist

12000

Quantity

15000

5

Pineapple

Dole

11000

6

Strawberry

Baguio

7000

7

Kiwi

Aussie

12000

Lookup Quantity

Jeorge · Jun 8, 2018

Hi Peter,
it did capture what I expected, thank you very much. Big help Sir.
added question what if accidentally it appeared (due to some error) two Banana on column A and Del Monte on column B is it possible to give me an error notice that there was two banana on column A and two Del Monte on column B? I tried filling in this situation and it captured the first Banana and Del Monte and ignored the succeding same value. Thanks again.

Peter_SSs · Jun 8, 2018

Something like this?

=IF(COUNTIFS($A$2:$A$10,F2,$B$2:$B$10,F3)=1,INDEX($C$2:$C$10,MATCH(F2&"|"&F3,INDEX($A$2:$A$10&"|"&$B$2:$B$10,0),0)),"Error")

or do you mean this?

=CHOOSE(MIN(COUNTIFS($A$2:$A$10,F2,$B$2:$B$10,F3),2)+1,"Not found",INDEX($C$2:$C$10,MATCH(F2&"|"&F3,INDEX($A$2:$A$10&"|"&$B$2:$B$10,0),0)),"Error")

Jeorge · Jun 9, 2018

Both works accordingly, you are great Sir, thank you very much.

Peter_SSs · Jun 9, 2018

Jeorge said:
Both works accordingly, you are great Sir, thank you very much.

You are welcome. (They don't do exactly the same job as each other though.)

reference 2 different cell and the result will com from the third cell

Jeorge

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

Jeorge

New Member

Peter_SSs

MrExcel MVP, Moderator

Jeorge

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

reference 2 different cell and the result will com from the third cell

Jeorge

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

Jeorge

New Member

Peter_SSs

MrExcel MVP, Moderator

Jeorge

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock