xlookup multiple criteria

[markomarra]

To Whom,

In a nutshell.

I have a column of stationing values that I would like to have the searched.

Find the interval between every PC and PT values in the search range and determine if another value is within that interval. If found return another value from a different column.

criteria:
1. PNT Type = "BL"
2. GEO. Type = ONLY the interval between PC and PT
3. BL = BL must Match Baseline
4. GEO. TIE = POT or POC

return value:
1. Radius Length.

Would prefer to use a excel formula. Maybe "xlookup" will work. but, only a suggestion/best guess. But if not possible a macro would suffice also.

Thanks in advance.

Mark

 

[awoohaw]

Hi @markomarra , welcome to the forum.
While images can help the forum see your problem and data, they really do not help forum help you effectively. The forum would need to manually recreate your scenario which takes time and is subject to typos.
The Mr. Excel Forum has a great tool called xl2bb add in (link below) that allows you to post mini worksheets. Please use that and post your sample data using that.
Be sure to sanitize your data for personal privacy concerns. If you cannot post a miniworksheet, the post your data as a table, but please be sure include the starting row and column number (and if you have any hidden, then please put a row/column number/letter on each column and row.

Thanks in advance.

 

[markomarra]

Hi awoohaw,

for security reasons i am unable to install software such as XL2BB. if there's and online conversion utility to convert xlsx to "mini-sheet" i can do that right away. Please send a link. Otherwise, it will have to wait until tomorrow before i repost using a mini-sheet.

Mark

 

[awoohaw]

you can copy and paste your data, just be sure there are row numbers and column headers. Please also give the formulas that are in any cells that may be important.
Please give expected values for the cell(s) in question.

 

[markomarra]

MrExcel xloopup.xlsx
	A	B	C	D	E	F	G	H	I
1	criteria 1	criteria 2	return value	search Range	criteria 3		criteria 4	value to be searched	criteria 3
2	PNT TYPE	GEO. TYPE	RADIUS	STA	BL		GEO. TIE	STA	BaseLine
3	BL	AP		9642.94	MAIN		PT	1244.77	AUX-C
4	BL	POT		10200.01	MAIN				AUX-C
5	BL	PC		10591.82	MAIN		PT	1244.77	AUX-C
6	BL	CC	900'		MAIN		PT	1244.77	AUX-C
7	BL	PT		10779.22	MAIN		POT	1333.89	AUX-C
8	BL	POE		11390.66	MAIN		POT	1333.89	AUX-C
9	BL	PC		37.89	AUX-C		POC	1351.9	AUX-C
10	BL	CC	300'		AUX-C		POC	1351.9	AUX-C
11	BL	PCC		169.58	AUX-C		PT	1424.81	AUX-C
12	BL	CC	120'		AUX-C		PT	1424.81	AUX-C
13	BL	PT		305.40	AUX-C		PT	1424.81	AUX-C
14	BL	POT		320.80	AUX-C		PT	1424.81	AUX-C
15	BL	PC		795.56	AUX-C		PT	1424.81	AUX-C
16	BL	CC	875'		AUX-C				AUX-C
17	BL	PT		846.10	AUX-C		PT	1424.81	AUX-C
18	BL	PC		1034.47	AUX-C		POT	1462	AUX-C
19	BL	CC	200'		AUX-C				AUX-C
20	BL	PT		1244.77	AUX-C		POT	1462	AUX-C
21	BL	PC		1337.95	AUX-C		POT	1462	AUX-C
22	BL	CC	400'		AUX-C		POT	1462	AUX-C
23	BL	PT		1424.81	AUX-C		POT	1462	AUX-C
24	BL	PC		1772.08	AUX-C		POT	1600	AUX-C
25	BL	CC	240'		AUX-C		POT	1600	AUX-C
26	BL	PT		1849.83	AUX-C		POT	1650	AUX-C
27	BL	POE		3642.87	AUX-B		POT	1650	AUX-C
28	BL	EQ		3600.00	AUX-B		POT	1650	AUX-C
29	BL	PT		3531.39	AUX-B		POT	1650	AUX-C
30	BL	CC	250'		AUX-B		POT	1650	AUX-C
31	BL	PC		3387.05	AUX-B		POT	1727	AUX-C
32	BL	PT		3220.55	AUX-B		POT	1727	AUX-C
33	BL	CC	168'		AUX-B		POT	1727	AUX-C
34	BL	PC		3081.12	AUX-B		PT	1849.83	AUX-C
35	BL	POB		3000.00	AUX-B		POT	1960	AUX-C
36	BL	POB		1000.00	AUX-A		POT	1960	AUX-C
37	BL	AP		1300.00	AUX-A		POT	1960	AUX-C
38	BL	PC		1537.56	AUX-A		POT	1960	AUX-C
39	BL	CC	250'		AUX-A				AUX-C
40	BL	PT		1885.88	AUX-A		PT	1849.83	AUX-C
41	BL	POE		2371.04	AUX-A		POT	1680	AUX-C
42	BL	PC		1727.22	AUX-D		POT	1680	AUX-C
43	BL	CC	270.00'		AUX-D		POT	1680	AUX-C
44	BL	PCC		1881.59	AUX-D		POT	1617.26	AUX-D
45	BL	CC	120.00'		AUX-D		POT	10975	MAIN
46	BL	PT		1949.83	AUX-D		POT	10976	MAIN
47	BL	PC		2026.56	AUX-D		POT	10968.86	MAIN
48	BL	CC	150.00'		AUX-D				MAIN
49	BL	PT		2119.30	AUX-D		POT	10868.88	MAIN
50	BL	POE		2180.77	AUX-D				MAIN
51	BL	POE		1998.58	AUX-C		POT	320.8	AUX-C
52	SL	AP		1000.00	AUX-C		POT	750	AUX-C
53	SL	AP		10975.00	MAIN				AUX-C
54	SL	AP		10975.00	MAIN		POT	750	AUX-C
55	SL	PC		10968.86	MAIN		POT	900	AUX-C
56	SL	CC	357'						AUX-C
57	SL	PT		10898.48	MAIN		POT	900	AUX-C
58	SL	PC		10591.83	MAIN		POT	1000	AUX-C
59	SL	CC	237'						AUX-C
Sheet1

 

[markomarra]

hi, i was able to use the Xl2bb addin. I hope i did it correctly.

example 1:
as for expected results cell h9 with a value of 1351.9 would be between the interval of:

PC [beginning of a curve] (station 1337.95) at cell D21 and
PT [begin tangent line] (station 1424.81) at cell D23
both are on the same Baseline "Aux-C"

therefore,
resulting in Radius 400' found in cell C22

example 2:
as for expected results cell h45 with a value of 10975.0 would be between the interval of:

PT (station 10779.22) at cell D7 and
POE (station 11390.66) at cell D8
both are on the same Baseline "MAIN"

because H45 is a POT (point on tangent aka straight line) not POC (point on curve)
resulting in Radius not found

mark

 

[awoohaw]

thanks. You'll probably get some responses now.

 

[awoohaw]

Okay, where is the calculation supposed to be performed? How are you choosing the lookup values? H45 is no where near the top?

 

[markomarra]

calculation formula will go in column F3 to F59 for each corresponding values in cells H3 to H59.

lookup each value in column H3 to H59.

mark

 

[awoohaw]

calculation formula will go in column F3 to F59 for each corresponding values in cells H3 to H59.

lookup each value in column H3 to H59.

mark

thanks!