Formula so as to Add Multply values in cell using a vlookup formula

NZAS

Board Regular
Joined
Oct 18, 2012
Messages
116
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I have a data set that I want to be able to return multiple values for this to the same cell. The data set has a list of pours that are stored in BINS. I want to be able to return using a vlookup or similar to populate those pours into the relevant cell in the matrix like below the data. I would only return 1 of each if there are multply pour values in the data set.
The data set can cover many pours and many Bin allocations. I hope there is way to be able to do this even if it not a vlookup as I though I would use. Currenlty I can only get 1 pour value.
Cheers
NZAS

POUR
RUN_COUNT
PRODUCT
ALLOY
WEIGHT
BINAREA
BIN
11823
9
3A949
AA190J
1023
STD1A
AC408
This data set has a lot of data listed of all POURS that are stored in Bins. I need a formula that will return list of the Pour values that are in the same Bin Number as shown below. I have a formula that I have used but only returns one of the pour numbers. This data is downloaded from a data base to excel. This gives a more visual view of where pours are stored
11823
4
3A949
AA190J
1023
STD1A
AC408
11846
1
3A949
AA190J
1023
STD1A
AC408
11823
3
3A949
AA190J
1024
STD1A
AC408
11823
8
3A949
AA190J
1025
STD1A
AC408
11823
6
3A949
AA190J
1027
STD1A
AC408
11823
11
3A949
AA190J
1027
STD1A
AC401
11823
2
3A949
AA190J
1027
STD1A
AC408
11823
14
3A949
AA190J
1027
STD1A
AC408
11823
5
3A949
AA190J
1027
STD1A
AC408
For the purpose of this the data displayed I have just 1 bin number with numerous pour values
11821
30
3A949
AA190J
1028
STD1A
AC408
11823
7
3A949
AA190J
1028
STD1A
AG404
11823
13
3A949
AA190J
1028
STD1A
AC408
Conditional Formatting criteria is used to hilight the cells that store the pours
Output for Casts
STD1A
401
402
403
404
405
406
407
408
409
410
411
AA
AB
AC
11823
11823 11846 11821
AD
11829
AE
AF
11881
AG
11823
AH

<tbody>
</tbody>
POUR
RUN_COUNT
PRODUCT
ALLOY
WEIGHT
BINAREA
BIN
11823
9
3A949
AA190J
1023
STD1A
AC408
This data
set has a lot of data listed
of all
POURS that are stored in Bins. I need a formula that will return list of the
Pour values that are in the same Bin Number as shown below.
The dataset is downloaded from a
data base to excel is updated. the matix below gives a more visual view of where pours are stored

11823
4
3A949
AA190J
1023
STD1A
AC408
11846
1
3A949
AA190J
1023
STD1A
AC408
11823
3
3A949
AA190J
1024
STD1A
AC408
11823
8
3A949
AA190J
1025
STD1A
AC408
11823
6
3A949
AA190J
1027
STD1A
AC408
11823
11
3A949
AA190J
1027
STD1A
AC401
11823
2
3A949
AA190J
1027
STD1A
AC408
11823
14
3A949
AA190J
1027
STD1A
AC408
11823
5
3A949
AA190J
1027
STD1A
AC408
For the
purpose of this the data displayed I have just 1 bin number with numerous
pour values

11821
30
3A949
AA190J
1028
STD1A
AC408
11823
7
3A949
AA190J
1028
STD1A
AG404
11823
13
3A949
AA190J
1028
STD1A
AC408
Conditional
Formatting
criteria is used to hilight
the cells that store the pours

Output for
Casts

STD1A
401
402
403
404
405
406
407
408
409
410
411
AA
AB
AC
11823
11823
11846 11821

AD
11829
AE
AF
11881
AG
11823
AH

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Flashbond

Well-known Member
Joined
Mar 13, 2010
Messages
670
In such scenario
POURRUN_COUNTPRODUCTALLOYWEIGHTBINAREABIN STD1A401402403404405406407408409410411
1182393A949AA190J1023STD1AAC408 AA
1182343A949AA190J1023STD1AAC408 AB
1184613A949AA190J1023STD1AAC408 AC11823 11823 11823 11846 11823 11823 11823 11823 11823 11823 11821 11823
1182333A949AA190J1024STD1AAC408 AD 11829
1182383A949AA190J1025STD1AAC408 AE
1182363A949AA190J1027STD1AAC408 AF 11881
11823113A949AA190J1027STD1AAC401 AG 11823
1182323A949AA190J1027STD1AAC408 AH
11823143A949AA190J1027STD1AAC408
1182353A949AA190J1027STD1AAC408
11821303A949AA190J1028STD1AAC408
1182373A949AA190J1028STD1AAG404
11823133A949AA190J1028STD1AAC408
:

<colgroup><col span="16"><col><col span="3"></colgroup><tbody>
</tbody>
{=IF(NOT($G1:$G14=$I4&Q$1),TEXTJOIN(" ",1,(EXACT($G1:$G14,$I4&Q$1),$A1:$A14,"")),"")}
Use this array fomula in Q4. It's an array formula so Ctrl+Shift+Enter after paste.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,156
Messages
5,623,079
Members
415,951
Latest member
Shen1986

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
Top