How to use number range in INDEX/MATCH?

excelos

Active Member
Joined
Sep 25, 2011
Messages
310
Hello!

I am using Index/Match and in the value to be matched, I want to specify the value of a cell +/-5%.

How do I specify that?

thx!
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
So given a Match value of 100 you want to match against a range for any value between 95 and 105 ? Yes?

Assuming there's only going to be one value returned

=INDEX(C1:C20,MATCH(1,(A1*95%<=B1:B20)*(A1*105%>=B1:B20),0),1)
Array formula, use Ctrl-Shift-Enter

where A1 is the value to match
B1:B20 is the range with to match against
C1:C20 is the value to return
 

excelos

Active Member
Joined
Sep 25, 2011
Messages
310
So given a Match value of 100 you want to match against a range for any value between 95 and 105 ? Yes?

Assuming there's only going to be one value returned

=INDEX(C1:C20,MATCH(1,(A1*95%<=B1:B20)*(A1*105%>=B1:B20),0),1)
Array formula, use Ctrl-Shift-Enter

where A1 is the value to match
B1:B20 is the range with to match against
C1:C20 is the value to return
Thanks but I don't quite follow.

I know the following:
=INDEX ( Column I want a return value from , MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0” ))

You put My lookup value as 1!

I am not sure you understand what I am talking about.
I want to return values that do not match exactly, but they are within a tolerance of the reference value.

Can you elaborate please?
 

excelos

Active Member
Joined
Sep 25, 2011
Messages
310
Btw, I tried your formula and I get
#VALUE!

Also there can be many matches and it can return the first.

<tbody>
</tbody>
 
Last edited:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
I have understood what you are talking about.

Look closely at what I said.

"Assuming there's only going to be one value returned"

And you've now added something that you omitted originally "I want to return values that do not match exactly, but they are within a tolerance of the reference value."
So this wont work for your problem.


"You put My lookup value as 1!"

Correct.
This is an array formula as specified earlier.
If A1*95%<=B1:B20 and A1*105%>=B1:B20 then a TRUE(1) is returned
This is then MATCHed against the lookup value of 1 (which I why it has to be 1). This results in a 1 in the array against the row number.
An INDEX is then performed in column C to return a value on that row.

Tested thus:

In a blank sheet put 100 in A1
in B1:B4 put the numbers 94, 95, 106, 107
in C1:C4 put the letters a, b, c, d

The only number that is within +-5% of A1 is 95 which is in row B2 so we want 2 returned, we can then INDEX() that on column C to return the value b.
If you put the formula

=MATCH(1,(A1*95%<=B1:B20)*(A1*105%>=B1:B20),0)
as an array formula
in a blank cell

it returns 2 - the correct row number.
So you just need to INDEX () column C with that formula get the b

The formula works perfectly but is based on returning only one value

You never specified in your original formula that you wanted to return more than one value.
You added that later.

The formula will only work for one value being returned.
So you cannot use this formula due to the additional information you provided after.
 
Last edited:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
#VALUE may be because you didnt enter it as an array formula.

Works fine for me
Supply some example data with expected output.
 
Last edited:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
Use this formula, this works.

put 100 in A1
with your number data in column B
in a blank column say C

=IFERROR(INDEX($B$1:$B$5,SMALL(IF((A$1*95%<=B$1:B$5)*(A$1*105%>=B$1:B$5),ROW($B$1:$B$5)),ROW(A1))-(ROW(A$1)-1),1),"")
Array formula, use Ctrl-Shift-Enter

Note this is an array formula

copy down the column
It will return values in column B that are within +-5% of the value in A1

Then just use an INDEX () on those values to retrieve any other data on that row.

Amend the formula to match the format of the data on your spreadsheet.
 

Forum statistics

Threads
1,081,989
Messages
5,362,581
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top