Sumproduct or Vlookup Help!

[Anbuselvam K]

Hi

Please find the attached file. In that file Batch number sheet i've used sumproduct formulation to get the no of batch number from master data sheet. i got the answer is 4 by counting the cells, but i need to get the values from that 4 cells (AB24, AB25, AB26, AB27).

My Input will be in Batch Number Sheet C3, I need result in C4 and C5.

Thanks in Advance!

 

[shrivallabha]

Hi,

Unfortunately, Excel can not output the batch information in one cell as you have indicated unless you are willing to go by VBA route.

However, if it is fine to have results in more than one cells then it is possible to do by formula route as well.

Edit: You can use the helper construct used by bosco_yip:
http://chandoo.org/forum/threads/lookup-result-with-multiple-values.27827/

 

[Anbuselvam K]

Hi

Thanks for your reply!

I've to lookup two values and then i need multiple results as like your links output. First Product Name has to be match then dispatch quantity should be zero and finally it has to display the batch number.

Please do the needful and send me the modified sheet.

 

[bosco_yip]

Try,

1] Master data Sheet, Helper Column S, S11 enter formula :

=IF(G11="",", "&C11&IFERROR(VLOOKUP(B11,B12:S$37,18,0),""),"")

and, Select S11>> Custom Cell Formatting >> in the Type box, enter : ;;;

Copy down.

2] Batch Number Sheet, C3 enter formula :

=IFERROR(MID(INDEX('Master data'!S11:S36,MATCH(1,INDEX(('Master data'!B11:B36=C3)*('Master data'!G11:G36=0),0),0)),3,250),"")

Regards
Bosco

 

[Anbuselvam K]

Thanks a lot for your help!

 

[bosco_yip]

Thanks a lot for your help!

Glad to help

 

[Anbuselvam K]

Hi,

As per your suggested formula, i got the value of 4 different dates in the below format.

,42396,42414,42414,42414

can you please guide me to get proper date format.

 

[bosco_yip]

Hi,

As per your suggested formula, i got the value of 4 different dates in the below format.

,42396,42414,42414,42414

can you please guide me to get proper date format.

Try, add a TEXT function inside the formula as in :

1] Master data Sheet, Helper Column S, S11 enter formula :

=IF(G11="",", "&TEXT(A11,"dd-mmm-yy")&IFERROR(TEXT(VLOOKUP(B11,B12:S$37,18,0),"dd-mmm-yy"),""),"")

Copy down.

2] Batch Number Sheet, C3 enter formula :

Same as post #4

Regards
Bosco

 

[Anbuselvam K]

I got it, Thanks a lot!

 

[bosco_yip]

I got it, Thanks a lot!

Thanks for feedback.

 

[Anbuselvam K]

hi

As you suggested i've used below formulation in Batch Number sheet Column C, C5.
=IFERROR(MID(INDEX('Master data'!V11:V36,MATCH(1,INDEX(('Master data'!C11:C36=C3)*('Master data'!J11:J36=0),0),0)),3,2500),"")

Actual Answer is AB13, 2166720568, 216672be0256. but i get only AB13.

Please guide to get the answer. Thanks in Advance!

 

[bosco_yip]

hi

As you suggested i've used below formulation in Batch Number sheet Column C, C5.
=IFERROR(MID(INDEX('Master data'!V11:V36,MATCH(1,INDEX(('Master data'!C11:C36=C3)*('Master data'!J11:J36=0),0),0)),3,2500),"")

Actual Answer is AB13, 2166720568, 216672be0256. but i get only AB13.

Please guide to get the answer. Thanks in Advance!

Find mistake in the helper column formula, which should be a 2 conditional lookup.

Formula fixed as in :

1] Master data Sheet, Helper Column S, S11 enter formula :

=IF(J11="",", "&D11&IFERROR(INDEX(V12:V$37,MATCH(1,INDEX((C12:C$37=C11)*(J12:J$37=""),0),0)),""),"")

and, Select S11>> Custom Cell Formatting >> in the Type box, enter : ;;;

Copy down.

2] Batch Number Sheet, C3 enter formula :

Same as post #4

Regards
Bosco

 

[Anbuselvam K]

It's working, Thanks for your support!

 

[bosco_yip]

It's working, Thanks for your support!

You're welcome.