Advise on excel formula approach please

[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, the returned Recommended Device should be "HP Laserjet Pro 400 M402dne"



Sheet 1 (A1:H6)

Model Serial Number Ref Colour / Mono Total Pages Total AMV Max RMPV Recommended Device
LaserJet 400 M401dn VNH6707013 M401dne Mono 3648 3000 3830
LaserJet 400 M401dn PHKGD19425 M401dne Mono 2010 3000 2111
LaserJet 400 M401dn VNH3902306 M401dne Mono 1232 3000 1294
LaserJet 400 M451dn CNFF308189 M451dn Colour 801 2000 841
LaserJet P2055dn CNCHB24129 P2055dn Mono 24 3000 25

Sheet 2 (A1:F20)

Model Name Colour / Mono Duty Cycle Min RMPV Max RMPV
M451dn HP LaserJet Pro 400 colour M451dn Colour 40000 750 2000
X451dn HP Officejet Pro X451dn Colour 50000 500 2800
M401dne HP Laserjet Pro 400 M401dne Mono 50000 750 3000
M425dn HP LaserJet Pro 400 MFP M425dn Mono 50000 750 3000
P2055dn HP Laserjet P2055 Mono Printer Mono 50000 750 3000
M402dne HP Laserjet Pro 400 M402dne Mono 80000 750 4000
M570dn HP LaserJet Pro 500 MFP M570dn Colour 75000 1500 4000
X551dw HP OfficeJet Pro X551dw Colour 75000 1000 4200
M551dn HP Enterprise M551dn Colour 75000 1500 5000
X585z HP Officejet Enterprise Color Flow MFP X585z Colour 80000 2000 6000
E58650z HP 58650 Colour 120000 2500 10000
M601 HP LaserJet Enterprise 600 M601 Series Mono 175000 3000 12000
M602dn HP LaserJet Enterprise 600 M602dn Mono 225000 3000 15000
M605dn HP LaserJet Enterprise M605dn Mono 225000 5000 16000
M603xh HP LaserJet Enterprise 600 M603xh Mono 275000 5000 20000
M712dn HP LaserJet Pro 700 M712dn Mono 100000 5000 20000
M855dn HP M855DN Printer Colour 175000 4000 25000
M880z HP Colour Laserjet Enterprise Flow MFP M880z Colour 200000 5000 25000
X451dw HP Laserjet X451dw Colour 50000 500 28000

Thank you in advance for looking at my post.

Peter

 

[AliGW]

Peter - grab yourself the ForumTools add-in (link in my signature) so that you can provide data that is easy to read and copy.

 

[hiker95]

clares,

We can not tell where your raw data is located, sheet name(s), cells, cell formatting, cell formulae, rows, columns, and, we can not tell where the results should be, sheet name, cells, cell formatting, cell formulae, rows, columns.


We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729

 

[Joe4]

Question re-posted here: https://www.mrexcel.com/forum/excel-questions/996596-help-advise-acheive-kind-lookup.html