Working with 2 Match Functions to locate & display the cell in a database

[itsrich]

On a pricing sheet

Currently the formula in use is =VLOOKUP(N27,'POK-Standards-and-Options'!$C:$XW,48,0) (there is a column of these used to price our boats)

I am looking to develop something more dynamic. I want to use the Boat Model in cell O24 and the Part Number in Column N to look up values in a database where the Model is in Row 14 & Part Numbers are in Column C.

The DB starts in F16:EL2081 (planning on reducing the size of this DB once this formula is developed)
Header Row is F14 through EL14
parent numbers in Column C

What I have tried...
=INDEX('POK-Standards-and-Options'!$C:$EL,MATCH(N28,'POK-Standards-and-Options'!$C:$C,0),MATCH($O$24,'POK-Standards-and-Options'!$14:$14,0)) results in ZERO. It should result in $945.

=INDIRECT(MATCH(N29,'POK-Standards-and-Options'!$C:$C,0),MATCH($O$24,'POK-Standards-and-Options'!$14:$14,0)) results in #REF!. The match functions give the correct row & column, but I am not sure I have the syntax of the indirect function correct.


Thank you for your help. I inherited this mess. The DB works well but the original creator has left and now I am tasked with bringing this spreadsheet up to 2023 standards.

 

[GAJITCS]

I can't visualise your data layout. Could you drop a small set of dummy data here please.

 

[itsrich]

This is a small sample of the DB.

POK-Standards-and-Options-Excel MY2023 TEST.xlsm
	A	B	C	D	E	F
14		No # necessary			ModelCode	BATX165
15	----------------	----------------	Model	----------------	W/NO ENGINE (penalized)	14495
16	Category ID	Category				0
17			CANVAS	CANVAS	NOTES	0
18		#'s for every size / color?	1	Bimini Top	Single purchased part	0
19		#'s for every size / color?	360	Bimini Top	Single purchased part	0
20		#'s for every size / color?	361	Bimini Top	Single purchased part	0
21		#'s for every size / color?	362	Bimini Top	Single purchased part	0
22		#'s for every size / color?	2	Mooring Cover (Dealer Installed)	Single purchased part	650
POK-Standards-and-Options

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:C1753,C1755:C1777,C2065:C1048576,C1985:C1993,C2014:C2018	Cell Value	duplicates	text	NO
C411:C1278,C1:C408,C1725:C1753,C1755:C1777,C2065:C1048576	Cell Value	duplicates	text	NO
C411:C1278,C1:C408,C1725:C1753,C1755:C1777,C2065:C1048576	Cell Value	duplicates	text	NO
C1155,C1092,C546:C716,C1:C34,C36:C40,C196:C406,C1223:C1278,C886:C989,C130:C194,C42:C126,C1725:C1753,C1755:C1777,C2065:C1048576	Cell Value	duplicates	text	NO
C1155,C1092,C546:C716,C177:C194,C27:C34,C36:C40,C1:C25,C196:C406,C1223:C1278,C886:C989,C130:C175,C42:C126,C1725:C1753,C1755:C1777,C2065:C1048576	Cell Value	duplicates	text	NO
BS82:CE82,P82:AJ82,F152:O154,DA87,DC87:DD87,P147:AA150,AC147:AD148,AC150:AD150,BS146:CE149,DU146:DX149,AW547:AW716,AW152:AX163,BA174:BB175,CY690:DK716,CI74:CJ74,DL201:DR203,DL53:DR53,AB152:AN154,F86:AN86,G87:AN87,G85:AN85,AQ26:BB26,W175:AN175,G151:AN151	Cell	contains a blank value	text	NO

This is a copy of the "Order form". Sorry, not to use the tool for posting. Excel locked for 10 minutes after starting it, so I gave up.

The yellow cell is O24 (B2 below) used in the formulas above. Every tab is a different model and this cell changes with every tab. The cell this column will go to is in the Total Price column 4 lines down to the 945.00 (call it D4)

The formula in D4 (column N in the formulas above) uses B2 & A4 and looks to the DB (POK-Standards-and-Options-Excel MY2023 TEST.xlsm). B2 matches a boat model in row 1 from column F to EL (look to F1 for the model, BATX165 does not match the FRV165SC, wide spreadsheet) A4 matches a part number in column C currently VLOOKUP function.

Am I chasing this modified Index/Match/vlookup idea the wrong way? Is there a better way? My predecessor developed a monster of a pricing spreadsheet. Sorry to make this so dificult.

 

[itsrich]

I need to reword/display this question better. I am closing this thread so I can work and present it an easier-to-understand format.