Possible VLOOKUP with IF Statements

[Derick_T]

Hi,
I'm looking to be able to build a report table based off of data from another table.

Basically, I'm looking to get the serial # for a certain piece of equipment if all my conditions are met in one cell, and calculate the age of the equipment based of the respective values for that same piece of equipment in another cell.

My thought was to leverage some IF statements to make this work but how to incorporate a function that will look to parallel cells within the IF (or vice versa; the IF being within a possible VLOOKUP) alludes me right now.

This may be simple enough to handle with pictures (below) but I also included the spreadsheet as well: https://drive.google.com/open?id=0B1mgDeo6i5j2MWtScDAzanotQzcxaFdRNDlJclZ5SEJXZzFB

Screenshots:
https://drive.google.com/file/d/0B1mgDeo6i5j2VmkwRXJtUm53OHg4YXhpWlM3SEU4alk2dFM0/view?usp=sharing

https://drive.google.com/file/d/0B1mgDeo6i5j2SWowd3UzamNJSHZyQUtNY0t2SVQwaVpicXU4/view?usp=sharing

Appreciate the help!

 

[glfiedler]

Derick, Because your equipment tracking table has multiple rows with equipment "in use" I think that using a formula such as Vlookup and IF statements would be difficult if not impossible. One of the really clever people on this forum may have a way to do that but I, not being clever, would create a VBA function. That function would return the serial #. You would pass the values of Series, Model and Position as arguments. Then a VBA code would loop through each row where the status is "in use" checking if the other three parameters are met. The coding would be a series of nested loops that would look at each parameter and determine the index (row) that meets all criteria. The function routine could call another routine that calculates the equipment's age. Do to my schedule I can not help you with the function coding for a few days. I'll check in a few days to see if someone else solved your problem.

 

[Derick_T]

Thanks for the quick response and the offer to help.

Would it make a difference (and I should have made this clear during my original problem statement) if there was only one serial #, that met my respective conditions and was "in use"?

In other words, there can only be serial # for a respective series, model and position.

Not sure if that if that helps but thought I would clarify.

Thanks.

 

[DanteAmor]

Try this:

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:104.55px;" /><col style="width:60.83px;" /><col style="width:72.24px;" /><col style="width:72.24px;" /><col style="width:72.24px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td colspan="5" style="background-color:#fcd5b4; color:#ffffff; font-weight:bold; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td colspan="2" rowspan="2" style="background-color:#d8d8d8; font-weight:bold; "> </td><td colspan="3" style="background-color:#d8d8d8; font-weight:bold; text-align:center; ">Series</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td colspan="3" style="background-color:#d8d8d8; font-weight:bold; text-align:center; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#d8d8d8; font-weight:bold; "> </td><td style="background-color:#d8d8d8; font-weight:bold; "> </td><td colspan="3" style="background-color:#d8d8d8; font-weight:bold; text-align:center; ">Model</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#d8d8d8; font-weight:bold; text-align:center; ">Position</td><td style="background-color:#d8d8d8; font-weight:bold; "> </td><td style="background-color:#d8d8d8; font-weight:bold; text-align:center; ">A</td><td style="background-color:#d8d8d8; font-weight:bold; text-align:center; ">B</td><td style="background-color:#d8d8d8; font-weight:bold; text-align:center; ">C</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td rowspan="2" style="background-color:#d8d8d8; font-weight:bold; text-align:center; ">1</td><td style="background-color:#d8d8d8; font-weight:bold; text-align:left; ">Serial #</td><td style="background-color:#ffc7ce; text-align:center; ">10001</td><td style="background-color:#ffc7ce; text-align:center; ">404</td><td style="text-align:center; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="background-color:#d8d8d8; font-weight:bold; text-align:left; ">Age</td><td style="background-color:#ffc7ce; text-align:center; ">10</td><td style="background-color:#ffc7ce; text-align:center; ">10</td><td style="text-align:center; ">ERROR</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td rowspan="2" style="background-color:#d8d8d8; font-weight:bold; text-align:center; ">2</td><td style="background-color:#d8d8d8; font-weight:bold; text-align:left; ">Serial #</td><td style="background-color:#ffc7ce; text-align:center; ">10002</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#d8d8d8; font-weight:bold; text-align:left; ">Age</td><td style="background-color:#ffc7ce; text-align:center; ">10</td><td style="text-align:center; ">ERROR</td><td style="text-align:center; ">ERROR</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td rowspan="2" style="background-color:#d8d8d8; font-weight:bold; text-align:center; ">3</td><td style="background-color:#d8d8d8; font-weight:bold; text-align:left; ">Serial #</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td><td style="text-align:center; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="background-color:#d8d8d8; font-weight:bold; text-align:left; ">Age</td><td style="text-align:center; ">ERROR</td><td style="text-align:center; ">ERROR</td><td style="text-align:center; ">ERROR</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C6</td><td >=SUMPRODUCT((Table1[Status/Life Span]="In Use")*(Table1[Series]=$C$3)*(Table1[Model]=C$5)*(Table1[Position]=$A6)*(Table1[Serial '#]))</td></tr><tr><td >C7</td><td >=IF(C6=0,"ERROR",DATEDIF(SUMPRODUCT((Table1[Status/Life Span]="In Use")*(Table1[Series]=$C$3)*(Table1[Model]=C$5)*(Table1[Position]=$A6)*(Table1[Date_Install])),TODAY(),"m"))</td></tr></table></td></tr></table> <br /><br />

 

[Derick_T]

That worked well Dante...thanks!

In my real life examples, it's very possible that my serial #'s will be a combination of numbers and letters and as I read up on that SUMPRODUCT function, it seems it is constrained to only numbers. ​

Any idea what the formula would need to look like to output the exact cell value regardless of it being text, numbers or a combination of both?

 

[DanteAmor]

Try:

=IFERROR(INDEX(Table1[Serial '#],SUMPRODUCT((Table1[Status/Life Span]="In Use")*(Table1[Series]=$C$3)*(Table1[Model]=C$5)*(Table1[Position]=$A6)*(ROW(Table1[Serial '#])))-ROW(Table1[[#All],[Serial '#]])),"ERROR")

=IF(C6="ERROR","ERROR",DATEDIF(SUMPRODUCT((Table1[Status/Life Span]="In Use")*(Table1[Series]=$C$3)*(Table1[Model]=C$5)*(Table1[Position]=$A6)*(Table1[Date_Install])),TODAY(),"m"))

 

[Derick_T]

That formula did the trick...thanks Dante!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.