Calculating MATCH row based on user input.

[chefdt]

I have a column of data in B from row 5 to 23 (see below). I want to accept user input in cell M25 and use that input to to select a MATCH row of data from my list below.

For example, if the user inputs $74, the row number is 3. (greater than 70, less than 80)

My column data is similar and listed below. The user would enter a value in M27 and the MATCH column would be calculated using the same assumptions.

For example, if the user inputs $1625, the MATCH column would be 5 (greater than 1500, less than 1750)

I'm new to INDEX and MATCH and even newer with trying to assign a value using user input. Thanks all.

ROW DATA in B[TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD] < $60[/TD]
[/TR]
[TR]
[TD="align: right"]$60[/TD]
[/TR]
[TR]
[TD="align: right"]$70[/TD]
[/TR]
[TR]
[TD="align: right"]$80[/TD]
[/TR]
[TR]
[TD="align: right"]$90[/TD]
[/TR]
[TR]
[TD="align: right"]$100[/TD]
[/TR]
[TR]
[TD="align: right"]$110[/TD]
[/TR]
[TR]
[TD="align: right"]$120[/TD]
[/TR]
[TR]
[TD="align: right"]$130[/TD]
[/TR]
[TR]
[TD="align: right"]$140[/TD]
[/TR]
[TR]
[TD="align: right"]$150[/TD]
[/TR]
[TR]
[TD="align: right"]$160[/TD]
[/TR]
[TR]
[TD="align: right"]$170[/TD]
[/TR]
[TR]
[TD="align: right"]$180[/TD]
[/TR]
[TR]
[TD="align: right"]$190[/TD]
[/TR]
[TR]
[TD="align: right"]$200[/TD]
[/TR]
[TR]
[TD="align: right"]$210[/TD]
[/TR]
[TR]
[TD="align: right"]$235[/TD]
[/TR]
[TR]
[TD="align: right"]$260[/TD]
[/TR]
</tbody>[/TABLE]

COLUMN DATA in row 4
[TABLE="width: 911"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]$500 [/TD]
[TD="class: xl65, width: 115, align: right"]$750 [/TD]
[TD="class: xl65, width: 64, align: right"]$1,000 [/TD]
[TD="class: xl65, width: 64, align: right"]$1,250 [/TD]
[TD="class: xl65, width: 64, align: right"]$1,500 [/TD]
[TD="class: xl65, width: 64, align: right"]$1,750 [/TD]
[TD="class: xl65, width: 64, align: right"]$2,000 [/TD]
[TD="class: xl65, width: 64, align: right"]$2,250 [/TD]
[TD="class: xl65, width: 64, align: right"]$2,500 [/TD]
[TD="class: xl65, width: 92, align: right"]$2,750 [/TD]
[TD="class: xl65, width: 64, align: right"]$3,000 [/TD]
[TD="class: xl65, width: 64, align: right"]$3,250 [/TD]
[TD="class: xl65, width: 64, align: right"]$3,500 [/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

1. Make your data consistent. Remove the <$60 and replace it with 0 (assuming the amount never goes negative)

Then use
=MATCH(M25,A1:A3,1)

2. MATCH(M27,A4:M4,1)

 

[chefdt]

1. Make your data consistent. Remove the <$60 and replace it with 0 (assuming the amount never goes negative)

Then use
=MATCH(M25,A1:A3,1)

2. MATCH(M27,A4:M4,1)

Thanks so much. Worked perfectly after I changed the references to B5:B23 as stated above. I didn't quite understand how the 1 vs 0 worked. Great and simple example of how to use.

DT

 

[Special-K99]

In a MATCH 0 indicates the value being searched for must be found in the range, e.g. MATCH(123,B5:B23,0) 123 must be in the range B5:B23 or there will be an error #N/A I think
If you change it to a 1 then the result will be the next value in the range LESS than the value being searched. So in your example it returns 70 (74 is not in the range.

 

[chefdt]

In a MATCH 0 indicates the value being searched for must be found in the range, e.g. MATCH(123,B5:B23,0) 123 must be in the range B5:B23 or there will be an error #N/A I think
If you change it to a 1 then the result will be the next value in the range LESS than the value being searched. So in your example it returns 70 (74 is not in the range.

Another question....If I wanted to highlight the cell that my INDEX(MATCH) equation references, how would I do that. Been watching some videos. Cecaus eI'm not matching exactly, conditional formatting seems out.

Thanks

 

[Special-K99]

You'd have to use Condtiional formatting (or VBA which i dont know much about).

Select the range B5:B23

Conditional Formatting
New Rule
Use a formula to determine...

=(B$5:B$23=M$25)

Format as required

Be aware if you have duplicate values of M25 in the B5:B23 range conditional formatting will highlight all of them, not just one.