Hi,
I need to do nested Vlook up to combine 3 vlookup formula together. The result is to find out the VBR% to match it with table 2, I would need to know the exact VBR% from table 2.
The start date is also important , because the table 2 have a mixed date by year 2018 and 2019 (no exact date). Meanwhile I actually split the table 2 according to year. One of the challenge is some of the line in table 2 does not contained the Material code. This is the original file that I extract from the SAP system.
I am not sure the best way to present my report. Should it be one column put it YES = match, another column as XX % when is not match??!
I have to do 6 conditions of Vlook up to find out the VBR%
1. End user + Distributor + Material code
2. End user + material
3. end user + distributor + MG1
4. end user + MG1
5. end user + distributor per agreement
6. end user itself
I can do the normal vlook up. Hence, what I really need here is the Nested Vlookup to for 3 vlookup "End user + distributor + Material code".
Your prompt reply is highly appreciated!!
The following table 1 showed the distributor code, end user code, PRM (Price Ref Material code) and VBR%
<tbody>
</tbody>
I need to do nested Vlook up to combine 3 vlookup formula together. The result is to find out the VBR% to match it with table 2, I would need to know the exact VBR% from table 2.
The start date is also important , because the table 2 have a mixed date by year 2018 and 2019 (no exact date). Meanwhile I actually split the table 2 according to year. One of the challenge is some of the line in table 2 does not contained the Material code. This is the original file that I extract from the SAP system.
I am not sure the best way to present my report. Should it be one column put it YES = match, another column as XX % when is not match??!
I have to do 6 conditions of Vlook up to find out the VBR%
1. End user + Distributor + Material code
2. End user + material
3. end user + distributor + MG1
4. end user + MG1
5. end user + distributor per agreement
6. end user itself
I can do the normal vlook up. Hence, what I really need here is the Nested Vlookup to for 3 vlookup "End user + distributor + Material code".
Your prompt reply is highly appreciated!!
The following table 1 showed the distributor code, end user code, PRM (Price Ref Material code) and VBR%
Distributor | End user | Start date | PRM | EU VBR % paid by distributor | |||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | |||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | 1 | |||||||
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> | 1 | |||||||
Table 2 | |||||||||||
End user | Distributor | Material code | Valid on Year | EU VBR % | MG1 | ||||||
80038405 | 28520413 | 2019 | 0.5% | c | |||||||
80038405 | 28520413 | 2018 | 1 | c | |||||||
80032648 | 28520420 | 6340999 | 2019 | 0.5 | d | ||||||
80032648 | 28520420 | 6340999 | 2018 | 2 | d | ||||||
80031775 | 28520261 | 28979099 | 2018 | 3 | c | ||||||
<tbody>
</tbody>