match cells >= and <=

C

coccio

Board Regular
Joined
Mar 19, 2002
Messages
156
Office Version
  1. 2016
<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCDEFGHIJKL
1************
2FtinweightGClass*******
341188FStandard*******
441189FStandard*Genderftinweight**
541190FStandard*f41188Standard*
641191FStandard*******
741192FStandard*******
8************
Sheet

</body></html>
I have a formula in K5 above it takes the G5, H5, I5, J5 and it matches with column A,B,C,D to get the result in K5. It works however in columns A,B, C, D, E I have a lot of data… this is a small sample. To reduce the data I would like to create a formula like the one above but in column C I would like to create a match function for weight that searches in between (greater than or less than) two sets of numbers like displayed below…How do I do that?

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCDEF
14FtinWeightWeightGClass
1541188115Fstandard
16411116174Fstandard+
174111755000Fdecline
Sheet


</body></html>
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Excel Workbook
ABCDEFGHIJK
1FtinWeightGClass*FtinWeightGClass
24110Fstandard*51140Fstandard+
3411116Fstandard+******
4411175Fdecline******
5500Fstandard******
650121Fstandard+******
750180Fdecline******
8510Fstandard******
951126Fstandard+******
1051185Fdecline******
11520Fstandard******
1252131Fstandard+******
1352190Fdecline******
14530Fstandard******
1553136Fstandard+******
1653195Fdecline******
17540Fstandard******
1854141Fstandard+******
1954200Fdecline******
Sheet1


Try this.

Basically, MATCH will look for the next weight smaller than the weight being searched for. So if you're greater than zero, but less than the 'standard+' weight, it returns standard. If you're over the max 'decline' weight, you're decline.

That way you don't have to keep two weights, and it's easy to manage.
 
Upvote 0
Alternatively:
Excel Workbook
ABCDEFGHIJKL
1FtinGstandardstandard+declineFtinWeightGClass
2411F011617553150Fstandard+
350F0121180
451F0126185
552F0131190
653F0136195
754F0141200
855F0146205
956F0151210
1057F0156215
1158F0161220
1259F0166225
13510F0171230
14511F0176235
Sheet2


Even shorter row-wise.
 
Upvote 0
<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCDEFGHIJKL
1ftinGstandardstandard+decline*ftinweightgclass
2411f0116175*411150F#N/A
350f0121180******
451f0126185******
552f0131190******
653f0136195******
754f0141200******
855f0146205******
956f0151210******
1057f0156215******
1158f0161220******
1259f0166225******
13510f0171230******
14511f0176235******
Sheet


</body></html>
Thanks good work The first formula works out great this one I was having trouble with couldn't get a result... I get #N/A maybe something I missing.
 
Upvote 0
<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCDEFGHIJKL
1ftinGstandardstandard+declineftinweightgclass
2411f011617541175fstandard
350f0121180
451f0126185
552f0131190
653f0136195
754f0141200
855f0146205
956f0151210
1057f0156215
1158f0161220
1259f0166225
13510f0171230
14511f0176235
Sheet


</body></html>

now it works out had to move down the column ranges
 
Upvote 0
<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCDEFGHIJKL
1ftinGstandardstandard+decline*ftinweightgclass
2411f88116175*53101mstandard
350f90156180******
451f93161187******
552f97168193******
653f100173199******
754f102179206******
855f106184212******
956f110190219******
1057f113196225******
1158f116201232******
1259f119207239******
13510f123213246******
14511f127220253******
1560f130225260******
1661f134233266******
Sheet


</body></html>

Something is off with this formula even when I input m in K2 it displays as standard. that shouldn't be
 
Upvote 0
Try this:
PHP: 
{=INDEX(D1:F1,1,MATCH(J2,OFFSET(D1:F1,MATCH(H2&I2&K2,A2:A16&B2:B16&C2:C16,0),0)))}
(I added a ,0 to the second match for exact match)
 
Upvote 0
You must log in or register to reply here.

Similar threads

X
  • Question
how to get data form html frame but not first div
Replies
1
Views
33
xyz987
X
R
  • Question
Help to sum all cells above until blank cell
Replies
3
Views
167
RemyMoo
R
L
  • Question
Return text from cell in column containing text, as long as values in other columns match several criteria
Replies
7
Views
349
etaf
etaf
T
  • Solved
Challenge Question: A Quest for Elegance
Replies
6
Views
254
Peter_SSs
Peter_SSs
W
  • Question
Table Lookup auto suggestions/return
Replies
0
Views
83
Wolfmanfafa
W

Forum statistics

Threads
1,214,621
Messages
6,120,568
Members
448,972
Latest member
Shantanu2024

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top