Help or Advise on to acheive a kind of lookup

[clares]

Hi All

Not sure how to acheive this so hoping somebody could help or advise.

Sheet 1 contains list of printers and what they have actually printed. The "Total Pages" is what the printer has printed over a three month period. I've calculated that the department that has the printer in the first row (Serial Number - VNH6707013) should really be using a printer that has a Max RMPV of 3830.

On Sheet 2 I have a list of printers and the manufacturers Max RMPV. Sheet 2 has been sorted by Max RMPV, where you have two mono printers that have the same Max RMPV, it should always be the first printer with the same Max RMPV. The only other important criteria would be if they currently have a mono printer I want to reccomend another mono printer, not a colour.

So looking at my printer with Serial Number VNH6707013, the returned Recommended Device should be "HP Laserjet Pro 400 M402dne"

Sheet 1

Excel 2016 (Windows) 64 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td=bgcolor:#99CCFF]Model[/td][td=bgcolor:#99CCFF]Serial Number[/td][td=bgcolor:#99CCFF]Ref[/td][td=bgcolor:#99CCFF]Colour / Mono[/td][td=bgcolor:#99CCFF]Total Pages Total AMV[/td][td=bgcolor:#99CCFF]Max RMPV[/td][td=bgcolor:#99CCFF]Recommended RMPV[/td][td=bgcolor:#99CCFF]Recommended Device[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]LaserJet 400 M401dn[/td][td]VNH6707013[/td][td]M401dne[/td][td]Mono[/td][td]3648[/td][td]3000[/td][td]3830[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]LaserJet 400 M401dn[/td][td]PHKGD19425[/td][td]M401dne[/td][td]Mono[/td][td]2010[/td][td]3000[/td][td]2111[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]LaserJet 400 M401dn[/td][td]VNH3902306[/td][td]M401dne[/td][td]Mono[/td][td]1232[/td][td]3000[/td][td]1294[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]LaserJet 400 color M451dn[/td][td]CNFF308189[/td][td]M451dn[/td][td]Colour[/td][td]801[/td][td]2000[/td][td]841[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]LaserJet P2055dn[/td][td]CNCHB24129[/td][td]P2055dn[/td][td]Mono[/td][td]24[/td][td]3000[/td][td]25[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Sheet 2

Excel 2016 (Windows) 64 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]Model[/td][td]Name[/td][td]Colour / Mono[/td][td]Duty Cycle[/td][td]Min RMPV[/td][td]Max RMPV[/td][td]Name[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]M451dn[/td][td]HP LaserJet Pro 400 colour M451dn[/td][td]Colour[/td][td]40000[/td][td]750[/td][td]2000[/td][td]HP LaserJet Pro 400 colour M451dn[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]X451dn[/td][td]HP Officejet Pro X451dn[/td][td]Colour[/td][td]50000[/td][td]500[/td][td]2800[/td][td]HP Officejet Pro X451dn[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]M401dne[/td][td]HP Laserjet Pro 400 M401dne[/td][td]Mono[/td][td]50000[/td][td]750[/td][td]3000[/td][td]HP Laserjet Pro 400 M401dne[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]M425dn[/td][td]HP LaserJet Pro 400 MFP M425dn[/td][td]Mono[/td][td]50000[/td][td]750[/td][td]3000[/td][td]HP LaserJet Pro 400 MFP M425dn[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td]P2055dn[/td][td]HP Laserjet P2055 Mono Printer[/td][td]Mono[/td][td]50000[/td][td]750[/td][td]3000[/td][td]HP Laserjet P2055 Mono Printer[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td]M402dne[/td][td]HP Laserjet Pro 400 M402dne[/td][td]Mono[/td][td]80000[/td][td]750[/td][td]4000[/td][td]HP Laserjet Pro 400 M402dne[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td]M570dn[/td][td]HP LaserJet Pro 500 MFP M570dn[/td][td]Colour[/td][td]75000[/td][td]1500[/td][td]4000[/td][td]HP LaserJet Pro 500 MFP M570dn[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td]X551dw[/td][td]HP OfficeJet Pro X551dw[/td][td]Colour[/td][td]75000[/td][td]1000[/td][td]4200[/td][td]HP OfficeJet Pro X551dw[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10​

[/td][td]M551dn[/td][td]HP Enterprise M551dn[/td][td]Colour[/td][td]75000[/td][td]1500[/td][td]5000[/td][td]HP Enterprise M551dn[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

11​

[/td][td]X585z[/td][td]HP Officejet Enterprise Color Flow MFP X585z[/td][td]Colour[/td][td]80000[/td][td]2000[/td][td]6000[/td][td]HP Officejet Enterprise Color Flow MFP X585z[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

12​

[/td][td]E58650z[/td][td]HP 58650[/td][td]Colour[/td][td]120000[/td][td]2500[/td][td]10000[/td][td]HP 58650[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

13​

[/td][td]M601[/td][td]HP LaserJet Enterprise 600 M601 Series[/td][td]Mono[/td][td]175000[/td][td]3000[/td][td]12000[/td][td]HP LaserJet Enterprise 600 M601 Series[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

14​

[/td][td]M602dn[/td][td]HP LaserJet Enterprise 600 M602dn[/td][td]Mono[/td][td]225000[/td][td]3000[/td][td]15000[/td][td]HP LaserJet Enterprise 600 M602dn[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

15​

[/td][td]M605dn[/td][td]HP LaserJet Enterprise M605dn[/td][td]Mono[/td][td]225000[/td][td]5000[/td][td]16000[/td][td]HP LaserJet Enterprise M605dn[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

16​

[/td][td]M603xh[/td][td]HP LaserJet Enterprise 600 M603xh[/td][td]Mono[/td][td]275000[/td][td]5000[/td][td]20000[/td][td]HP LaserJet Enterprise 600 M603xh[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

17​

[/td][td]M712dn[/td][td]HP LaserJet Pro 700 M712dn[/td][td]Mono[/td][td]100000[/td][td]5000[/td][td]20000[/td][td]HP LaserJet Pro 700 M712dn[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

18​

[/td][td]M855dn[/td][td]HP M855DN Printer [/td][td]Colour[/td][td]175000[/td][td]4000[/td][td]25000[/td][td]HP M855DN Printer [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

19​

[/td][td]M880z[/td][td]HP Colour Laserjet Enterprise Flow MFP M880z[/td][td]Colour[/td][td]200000[/td][td]5000[/td][td]25000[/td][td]HP Colour Laserjet Enterprise Flow MFP M880z[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

20​

[/td][td]X451dw[/td][td]HP Laserjet X451DW[/td][td]Colour[/td][td]50000[/td][td]500[/td][td]28000[/td][td]HP Laserjet X451DW[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet 2[/td][/tr][/table]

I can re-organise the data in either worksheet to suit.

Once again, thank you in advance for looking at my post.

Kind Regards

Peter

 

[James006]

Hello Peter,

In Sheet 1, formula for cell H2 would be :

=INDEX(Sheet2!$G$1:$G$20,MATCH(D2&F2,Sheet2!$C$1:$C$20&Sheet2!$F$1:$F$20,0))

As an Array Formula, you should use simultaneously the keys Control+Shift+Enter ... instead of the Enter key ...

Hope this will help