match cells >= and <=

coccio

Board Regular
Joined
Mar 19, 2002
Messages
143
<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 does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
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.
 

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
Alternatively:
Excel Workbook
ABCDEFGHIJKL
1FtinGstandardstandard+declineFtinWeightGClass
2411F011617553150Fstandard+
350F0121180
451F0126185
552F0131190
653F0136195
754F0141200
855F0146205
956F0151210
1057F0156215
1158F0161220
1259F0166225
13510F0171230
14511F0176235
Sheet2


Even shorter row-wise.
 

coccio

Board Regular
Joined
Mar 19, 2002
Messages
143
<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.
 

coccio

Board Regular
Joined
Mar 19, 2002
Messages
143

ADVERTISEMENT

<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
 

coccio

Board Regular
Joined
Mar 19, 2002
Messages
143
<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
 

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
Try this:
Code:
{=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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,517
Messages
5,832,211
Members
430,116
Latest member
d1pakjha

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
Top