Need help with a multiple criteria lookup formula

I have been trying to create a index match or lookup formula that will display prices baised on a part name and size. I cant seem to figure out the syntax to make a formula that will search the intire price book and produce correct prices. I have attached two versions of the price book one sorted virticly and the other in horisontal tables. Is it posable to make a formula that will display all of the prices for each tool baised on name and size? I can make a vlookup or index match that will work one tool but if i try to set the search aria to more than one tool with sizes I cant get it to come up with the correct list of prices. I need a formula kind of like the one I made to search grapple prices


=IFERROR("$"&INDEX(IF(SUM(COUNTIF($C23,{"*BASKET GRAPPLE &*"}))>0,'Series 150 B.O.S Gr'!$B$3:$B$75,IF(SUM(COUNTIF($C23,{"*SPIRAL GRAPPLE &*"}))>0,'Series 150 B.O.S Gr'!$D$3:$D$75)),MATCH(B23,'Series 150 B.O.S Gr'!$A$3:$A$75,0)),"$"&INDEX(IF(SUM(COUNTIF($C23,{"*PLAIN CONTROL*","*PLAIN BASKET GRAPPLE CONTROL*"}))>0,'Series 150 B.O.S Gr'!$I$3:$I$75,IF(SUM(COUNTIF($C23,{"*SPIRAL CONTROL*"}))>0,'Series 150 B.O.S Gr'!$H$3:$H$75)),MATCH(B23,'Series 150 B.O.S Gr'!$A$3:$A$75,0)))

The grapple price chart only has one criteria to search and no duplicate sizes.

Can someone show me how to create a lookup formula that will display the entire row or colum of priced for a tool baised on tool name and size? most of the tools are priced per O.D. inch so the search name will be either the excact name of the tool or part of the name with wild cards but the size will be randum.
I am using excel 2013.

Thanks in advance for any attempts at helping me with this formula

this is the price list used with the grapple formula

This is a simplified version of the main problem I am having
shrinking the search criteria to the data at the bottom of this post

=INDEX(D5:D22,MATCH("*BOOT COLLAR*",A5:A22,0)*(MATCH(9,B5:B22,1)))
The above formula returns the correct first day price for a 9" boot basket $808.00

But the below formula returns a reference #REF! Error.
=INDEX(D5:D22,MATCH("*JUNK GLOBE*",A5:A22,0)*(MATCH(9,B5:B22,1)))

I think the second formula is adding the position of both matches of 9 resulting in a cell out
of the specified range but I don't know how to stop it from doing that.



BASKET - BOOT COLLAR 3 3/4 F.D. 453.00 A.D. $51.00 Sb. P.D 7.55 F-2
BASKET - BOOT COLLAR2 4 1/4 F.D. 477.00 A.D. $58.00 Sb. P.D 7.95 F-2
BASKET - BOOT COLLAR3 4 3/4 F.D. 598.00 A.D. $68.00 Sb. P.D $9.97 F-2
BASKET - BOOT COLLAR4 5 3/4 F.D. 666.00 A.D. $68.00 Sb. P.D 11.10 F-2
BASKET - BOOT COLLAR5 6 1/8 F.D. 715.00 A.D. $78.00 Sb. P.D 11.92 F-2
BASKET - BOOT COLLAR6 6 3/4 F.D. 759.00 A.D. $78.00 Sb. P.D 12.65 F-2
BASKET - BOOT COLLAR7 7 3/4 F.D. 808.00 A.D. $88.00 Sb. P.D 13.47 F-2
BASKET - BOOT COLLAR8 9 3/4 F.D. 1217.00 A.D. $124.00 Sb. P.D 20.28 F-2
BASKET - BOOT COLLAR9 9 19/25 F.D. 1515.00 A.D. $152.00 Sb. P.D 25.25 F-2
BASKETS - JUNK GLOBE 7 P.R. 2946.00 Sb. P.D $49.10 F-2
BASKETS - JUNK GLOBE2 7 7/8 P.R. 3307.00 Sb. P.D $55.12 F-2
BASKETS - JUNK GLOBE3 8 3/4 P.R. 3671.00 Sb. P.D $61.18 F-2
BASKETS - JUNK GLOBE4 9 7/8 P.R. 4215.00 Sb. P.D $70.25 F-2
BASKETS - JUNK GLOBE5 10 3/4 P.R. 4535.00 Sb. P.D $75.58 F-2
BASKETS - JUNK GLOBE6 12 1/4 P.R. 5332.00 Sb. P.D $88.87 F-2
BASKETS - JUNK GLOBE7 14 1/2 P.R. 7150.00 Sb. P.D $119.17 F-2
BASKETS - JUNK GLOBE8 16 1/4 P.R. 8082.00 Sb. P.D $134.70 F-2
BASKETS - JUNK GLOBE9 17 1/2 P.R. 9104.00 Sb. P.D $151.73 F-2

I think I may have fond a way to solve my problem using this sub array formula to remove all the tool sizes that are not <= the size I enter.


=IFERROR(INDEX('TEMP PRICE BOOK'!A$1:A$346,SMALL(IF('TEMP PRICE BOOK'!$B$1:$B$346<=Sheet3!$A$2,ROW('TEMP PRICE BOOK'!A$1:A$346)-ROW('TEMP PRICE BOOK'!A$1)+1),ROWS('TEMP PRICE BOOK'!A$1:A1))),"")

First I enter the above formula into a blank worksheet and drag and copy the formula to overlap the ranges in my price book. Then put the size of tool I am searching for in Sheet3!A2 And put the tool type in Sheet3!A3.


Then invert my list of prices and use this index match

=INDEX(D5:D354,MATCH(A3,A5:A354,0))

It seems to work fine but because of some of the calculations and formulas I used in my LONG path to get my prices any price that is an endless fraction like 100/3=33.33333333 shows up with all eight digits behind the decimal point how do I change it back to two digits 33.33?? I tried changing the number format of the cell but it has no effect .

I am still very new to excel I only started playing around with it about 3 or 4 weeks ago so I don’t know the syntax to tell excel what I want it to do. There is probably a much shorter less convoluted way to get the results I am looking for.

Sorry for all the long posts but I am not very good at explaining what I am trying to do.

Well I seem to be just talking to myself, 111 views and no one has any input or suggestions??

will someone help me figure out why the formula in column D is pulling 8 for every fraction ending in 8 instead of the complete text string listed in the search criteria?

it appears that when 8 is the largest digit in the string it returns 8 instead of 3-5/8 so how do i fix this?