match cells >= and <=

coccio

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

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

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
140
<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
140

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
140
<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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,606
Messages
5,597,134
Members
414,128
Latest member
Jorglo

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