Help or Advise on to acheive a kind of lookup

clares

Well-known Member
Joined
Mar 14, 2002
Messages
557
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
A
B
C
D
E
F
G
H
1
ModelSerial NumberRefColour / MonoTotal Pages Total AMVMax RMPVRecommended RMPVRecommended Device
2
LaserJet 400 M401dnVNH6707013M401dneMono364830003830
3
LaserJet 400 M401dnPHKGD19425M401dneMono201030002111
4
LaserJet 400 M401dnVNH3902306M401dneMono123230001294
5
LaserJet 400 color M451dnCNFF308189M451dnColour8012000841
6
LaserJet P2055dnCNCHB24129P2055dnMono24300025
Sheet: Sheet1

Sheet 2

Excel 2016 (Windows) 64 bit
A
B
C
D
E
F
G
1
ModelNameColour / MonoDuty CycleMin RMPVMax RMPVName
2
M451dnHP LaserJet Pro 400 colour M451dnColour400007502000HP LaserJet Pro 400 colour M451dn
3
X451dnHP Officejet Pro X451dnColour500005002800HP Officejet Pro X451dn
4
M401dneHP Laserjet Pro 400 M401dneMono500007503000HP Laserjet Pro 400 M401dne
5
M425dnHP LaserJet Pro 400 MFP M425dnMono500007503000HP LaserJet Pro 400 MFP M425dn
6
P2055dnHP Laserjet P2055 Mono PrinterMono500007503000HP Laserjet P2055 Mono Printer
7
M402dneHP Laserjet Pro 400 M402dneMono800007504000HP Laserjet Pro 400 M402dne
8
M570dnHP LaserJet Pro 500 MFP M570dnColour7500015004000HP LaserJet Pro 500 MFP M570dn
9
X551dwHP OfficeJet Pro X551dwColour7500010004200HP OfficeJet Pro X551dw
10
M551dnHP Enterprise M551dnColour7500015005000HP Enterprise M551dn
11
X585zHP Officejet Enterprise Color Flow MFP X585zColour8000020006000HP Officejet Enterprise Color Flow MFP X585z
12
E58650zHP 58650Colour120000250010000HP 58650
13
M601HP LaserJet Enterprise 600 M601 SeriesMono175000300012000HP LaserJet Enterprise 600 M601 Series
14
M602dnHP LaserJet Enterprise 600 M602dnMono225000300015000HP LaserJet Enterprise 600 M602dn
15
M605dnHP LaserJet Enterprise M605dnMono225000500016000HP LaserJet Enterprise M605dn
16
M603xhHP LaserJet Enterprise 600 M603xhMono275000500020000HP LaserJet Enterprise 600 M603xh
17
M712dnHP LaserJet Pro 700 M712dnMono100000500020000HP LaserJet Pro 700 M712dn
18
M855dnHP M855DN Printer Colour175000400025000HP M855DN Printer
19
M880zHP Colour Laserjet Enterprise Flow MFP M880zColour200000500025000HP Colour Laserjet Enterprise Flow MFP M880z
20
X451dwHP Laserjet X451DWColour5000050028000HP Laserjet X451DW
Sheet: Sheet 2

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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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
Back
Top