Vlookup ---> then QTY Extact match or Greater Qty

[MUKESHY12390]

Hi All

I'm trying to build formula to lookup one column (Product) (has Duplicate value in Table Array) and if "Product" matches with lookup table ("A:B") then ---> it should look for Exact Qty or Greater Qty (column B) but the first preference is "Exact match"

for reference I have uploaded file to below link.


https://www.dropbox.com/s/3jtyr4mtg0al43q/MrExcel_Question.xlsx?dl=0


Thanks,
Mukesh

 

[Marcelo Branco]

Try


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Product Name​

[/td][td]

Qty​

[/td][td][/td][td]

Product Name​

[/td][td]

QTY​

[/td][td]

Expected​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Alconol 500mg (4's)​

[/td][td]

50​

[/td][td][/td][td]

Alconol 500mg (4's)​

[/td][td]

40​

[/td][td]

50​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Alconol 500mg (4's)​

[/td][td]

30​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Accufine 20mg (10's)​

[/td][td]

40​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Accufine 30mg (10's)​

[/td][td]

80​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Accufine 30mg (10's)​

[/td][td]

90​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Accufine 5mg (10's)​

[/td][td]

40​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Acenext 100mg (10's​

[/td][td]

20​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in F2
=AGGREGATE(15,6,B$2:B$8/((A$2:A$8=D2)*(B$2:B$8>=E2)),1)

M.

 

[MUKESHY12390]

Thanks for the quick reply. I will check and confirm.

 

[MUKESHY12390]

thanks , I amazed it works exactly I wanted.

Is is possible to you if you could explain the formula specially this part "B$2:B$8/((A$2:A$8=D2)*(B$2:B$8>=E2))".

that would very helpful.

Thank you so much!!

 

[Marcelo Branco]

You are welcome. Glad to help.

To understand what the formula does, step by step, try
Formulas > Evaluate formula

M.

 

[MUKESHY12390]

Already tried that going to give my best again.

 

[MUKESHY12390]

Understood now....I'm so intelligent .This is the first time I used AGGREGATE function.

Thanks Again Branco.

 

[Marcelo Branco]

You are welcome.

M.