how to do Nested Vlookup for 3 vloop up formula together?

[Goddard]

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%
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Distributor[/TD]
[TD]End user[/TD]
[TD]Start date[/TD]
[TD]PRM[/TD]
[TD]EU VBR % paid by distributor[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 219"]
<tbody>[TR]
[TD="width: 219, align: right"]28520413[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD="width: 107, align: right"]80038405[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 173"]
<tbody>[TR]
[TD="class: xl66, width: 173, align: right"]01/03/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD="class: xl65, width: 176, align: right"]8569999[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 153"]
<tbody>[TR]
[TD="class: xl65, width: 153, align: right"]0.5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 219"]
<tbody>[TR]
[TD="width: 219, align: right"]28520261[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD="width: 107, align: right"]80031775[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 173"]
<tbody>[TR]
[TD="class: xl66, width: 173, align: right"]01/04/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD="class: xl65, width: 176, align: right"]28979099[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 153"]
<tbody>[TR]
[TD="class: xl65, width: 153, align: right"]0.01[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 219"]
<tbody>[TR]
[TD="width: 219, align: right"]28520420[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD="width: 107, align: right"]80032648[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 173"]
<tbody>[TR]
[TD="class: xl66, width: 173, align: right"]01/04/2019[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD="class: xl65, width: 176, align: right"]6340999[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 219"]
<tbody>[TR]
[TD="width: 219, align: right"]28520420[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD="width: 107, align: right"]80032648[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 173"]
<tbody>[TR]
[TD="class: xl66, width: 173, align: right"]01/11/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD="class: xl65, width: 176, align: right"]6340999[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Table 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]End user[/TD]
[TD]Distributor[/TD]
[TD]Material code[/TD]
[TD]Valid on Year[/TD]
[TD]EU VBR %[/TD]
[TD]MG1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]80038405[/TD]
[TD]28520413[/TD]
[TD][/TD]
[TD]2019[/TD]
[TD]0.5%[/TD]
[TD]c[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]80038405[/TD]
[TD]28520413[/TD]
[TD][/TD]
[TD]2018[/TD]
[TD]1[/TD]
[TD]c[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]80032648[/TD]
[TD]28520420[/TD]
[TD]6340999[/TD]
[TD]2019[/TD]
[TD]0.5[/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]80032648[/TD]
[TD]28520420[/TD]
[TD]6340999[/TD]
[TD]2018[/TD]
[TD]2[/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]80031775[/TD]
[TD]28520261[/TD]
[TD]28979099[/TD]
[TD]2018[/TD]
[TD]3[/TD]
[TD]c[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]