AND/OR help

[Waltc_Diamond]

Hi: I have a list of vehicles of various mileage and I'm trying to write a formula that compares cells going across columns for future years, with vehicle total miles and returns the year that the vehicle meets or exceeds 150,000. I tried using an if(and(formula, but it gave me an error if the vehicle exceeded 150k before the first year, or didn't meet or exceed by the last year.

Here's a sample of my table.

Thanks!! Walt

 

[kweaver]

Welcome to Mr. Excel,

Please use XL2BB to show your sample data so that anyone trying to help you doesn't have to try to recreate the data.
Also, what do you want it to show in the "Retire Year" if the initial miles are >150K

 

[Toadstool]

Hi Walt,

Does this do what you want?

WaltcDiamond.xlsx
	C	D	E	F	G	H
4	Avg Ann miles	Est Miles	Est Miles	Est Miles	Est Miles
5	2021	2022	2023	2024	2025	Retire Year
6	134581	184920	215523	275818	343342	2022
7	94906	134127	148493	171698	208079	2024
8	57278	63432	76244	111476	140405	After 2025
9	106267	150254	205330	241078	334890	2022
10	68785	99356	113204	144790	160011	2025
11	43694	51674	65322	66837	70226	After 2025
12	97996	121009	164775	215966	283217	2023
13	0	33828	75526	102420	106845	After 2025
14	44224	57144	66743	84424	92410	After 2025
15	97257	119552	157864	221165	280078	2023
16	163717	199246	209081	302373	330428	2021
17	181524	217567	269217	317659	442093	2021
Sheet1

Cell Formulas
Range	Formula
E5:G5	E5	=D5+1
H6:H17	H6	=IFERROR(INDEX($C$5:$G$5,AGGREGATE(15,6,COLUMN($C6:$G6)-COLUMN($B6)/($C6:$G6>=150000),1)),"After "&G$5)

 

[kweaver]

Is this close?

Book1
	A	B	C	D	E	F
1	Initial	2022	2023	2024	2025	Year
2	53000.81	84557.81	106866.8	128065.8	157617.8	2025
3	86145.33	122822.3	171553.3	199505.3	220476.3	2023
4	159795.1	208292.1	236569.1	266963.1	288912.1	Initial
5	154810.6	202671.6	239732.6	269903.6	314657.6	Initial
6	147237.4	185971.4	214007.4	241118.4	266887.4	2022
7	159762.9	200627.9	246269.9	285072.9	318160.9	Initial
8	22159.25	43173.25	86958.25	116348.3	159267.3	2025
9	119841.5	154668.5	201354.5	242775.5	276045.5	2022
10	145204.5	165276.5	213106.5	235258.5	259275.5	2022
Sheet2

Cell Formulas
Range	Formula
F2:F10	F2	=INDEX($A$1:$E$1,MATCH(TRUE,A2:E2>150000,0))

 

[Waltc_Diamond]

That's perfect toadstool. Thank you.

Walt

 

[Toadstool]

That's perfect toadstool. Thank you.
Walt

You're welcome.
Thanks for the feedback.

 

[Waltc_Diamond]

New, but related question, I have a list of vehicles by make and model, and a table of make/model and msrp, and I'm trying to write a vlookup formula that looks at both criteria to get the msrp. For example, in column c is the make (Chev, Ford etc) and column d is the model. In the table, I need the formula to find the make & model and enter the appropriate msrp.

Thanks!!

A B C D E ASSET# YEAR MAKE MODEL MILEAGE 12/31/2019 CHEV 1500 800 12/31/2012 DODGE 3500 81008 12/31/2018 CHEV 2500 86180​ 81012 12/31/2018 CHEV 2500 88180​ 81017 12/31/2018 CHEV 3500 108032​ 81020 12/31/2018 CHEV TAHOE 86224​ 81021 12/31/2019 CHEV 1500 134430​		MAKE MODEL MSRP CHEV 1500 35000​ CHEV 2500 37500​ CHEV 3500 40000​ FORD F150 32000​ FORD F250 35000​ FORD F350 38000​ DODGE 1500 35000​ DODGE 2500 38000​
				​

 

[Toadstool]

Yes, you should probably start a new post so others have the chance to answer, but here's my take.

Firstly, I'm not sure how you can establish an MSRP without your table having a model year, e.g. the Chevrolet 1500 has been around over 20 years, but I'll ignore that and answer your VLOOKUP question.

This is interesting as the normal way in Excel 2016 to find data in a table with multiple criteria would be to use INDEX, MATCH, INDEX or even my old favorite AGGREGATE, but you have an interesting challenge so I'm going to use the old method of adding a helper column.

You challenge is that some data such as Model may be interpreted as numeric or text (e.g. 2500). As long as you force your Asset list and lookup table to be text so TAHOE and 3500 are both treated as text, then it works, but if we use a helper column it forces both criteria to be text.

I've added the Concat column (to the left of MSRP because VLOOKUP can't pickup columns to the left) to concatenate Make and Model. Then the VLOOKUP can just concatenate the Asset Make and Model to perform the lookup.

Let me know if you'd also like to see an INDEX, MATCH, INDEX or AGGREGATE approach.

WaltcDiamond.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	ASSET#	YEAR	MAKE	MODEL	MILEAGE	MSRP		MAKE	MODEL	Concat	MSRP
2		12/31/2019	CHEV	1500		35000		CHEV	1500	CHEV1500	35000
3	800	12/31/2012	DODGE	3500		No match		CHEV	2500	CHEV2500	37500
4	81008	12/31/2018	CHEV	2500	86180	37500		CHEV	3500	CHEV3500	40000
5	81012	12/31/2018	CHEV	2500	88180	37500		FORD	F150	FORDF150	32000
6	81017	12/31/2018	CHEV	3500	108032	40000		FORD	F250	FORDF250	35000
7	81020	12/31/2018	CHEV	TAHOE	86224	No match		FORD	F350	FORDF350	38000
8	81021	12/31/2019	CHEV	1500	134430	35000		DODGE	1500	DODGE1500	35000
9						0		DODGE	2500	DODGE2500	38000
10
2nd Question

Cell Formulas
Range	Formula
F2:F9	F2	=IFERROR(VLOOKUP(C2&D2,$J$2:$K$9999,2,0),"No match")
J2:J10	J2	=H2&I2

 

[Waltc_Diamond]

Actually the "Year" column B, denotes the model year. The concatenate column and changing the model column to text worked, but if you have time, I'd appreciate seeing an INDEX, MATCH, INDEX and and AGGREGATE approach. I'm not familiar with any of those.

Thanks so much for the help.

 

[Toadstool]

OK, here's the INDEX MATCH INDEX solution.

WaltcDiamond.xlsx
	A	B	C	D	E	F	G	H	I	J
1	ASSET#	YEAR	MAKE	MODEL	MILEAGE	MSRP		MAKE	MODEL	MSRP
2		12/31/2019	CHEV	1500		35000		CHEV	1500	35000
3	800	12/31/2012	DODGE	3500		No match		CHEV	2500	37500
4	81008	12/31/2018	CHEV	2500	86180	37500		CHEV	3500	40000
5	81012	12/31/2018	CHEV	2500	88180	37500		FORD	F150	32000
6	81017	12/31/2018	CHEV	3500	108032	40000		FORD	F250	35000
7	81020	12/31/2018	CHEV	TAHOE	86224	No match		FORD	F350	38000
8	81021	12/31/2019	CHEV	1500	134430	35000		DODGE	1500	35000
9						0		DODGE	2500	38000
10
2nd INDEXMATCHINDEX

Cell Formulas
Range	Formula
F2:F9	F2	=IFERROR(INDEX($J$2:$J$999,MATCH(1,INDEX(($H$2:$H$999=C2)*($I$2:$I$999=D2),0,1),0)),"No match")

Here's the AGGREGATE solution

WaltcDiamond.xlsx
	A	B	C	D	E	F	G	H	I	J
1	ASSET#	YEAR	MAKE	MODEL	MILEAGE	MSRP		MAKE	MODEL	MSRP
2		12/31/2019	CHEV	1500		35000		CHEV	1500	35000
3	800	12/31/2012	DODGE	3500		No match		CHEV	2500	37500
4	81008	12/31/2018	CHEV	2500	86180	37500		CHEV	3500	40000
5	81012	12/31/2018	CHEV	2500	88180	37500		FORD	F150	32000
6	81017	12/31/2018	CHEV	3500	108032	40000		FORD	F250	35000
7	81020	12/31/2018	CHEV	TAHOE	86224	No match		FORD	F350	38000
8	81021	12/31/2019	CHEV	1500	134430	35000		DODGE	1500	35000
9						0		DODGE	2500	38000
10
2nd AGGREGATE

Cell Formulas
Range	Formula
F2:F9	F2	=IFERROR(INDEX($J$2:$J$999,AGGREGATE(15,6,ROW($H$2:$H$999)-ROW($H$1)/(($H$2:$H$999=C2)*($I$2:$I$999=D2)),1)),"No match")