INDEX MATCH trying to return a value between two criteria.

**cyberkidkiller** · 03-19-2014, 09:04 AM

Hi I am having a problem with returning a value from a table when the value is between two numbers
I have the table on sheet 1
A B C
1 1,693,000 1,713,001 1,722,001
2 1,713,000 1,722,000 1,731,000
3

And other on sheet 2
A B
1 1,700,000 A1
2 1,720,000 A2
3 1,730,000 A3
4

And I want my equation to return the value from sheet 2 Column B if the number in sheet 2 column A lies between the numbers in sheet 1 rows 1 and 2.
For example sheet 1 A3 would be populated with A1 as the number 1,700,000 (from cell sheet2 a1) as it is between 1,693,000 and 1,713,000.

Any help will be much appreciated as this is starting to hurt my brain.

cheers

**Tony Valko** · 03-19-2014, 09:27 AM

I think this is what you want (but not certain!)...

Sheet2

	A	B
1	1,700,000	A1
2	1,720,000	A2
3	1,730,000	A3

Sheet1

	A	B	C
1	1,693,000	1,713,001	1,722,001
2	1,713,000	1,722,000	1,731,000
3
4	A1	A2	A3

This array formula** entered in A4 and copied across:

=INDEX(Sheet2!$B1:$B3,MATCH(1,(A1<=Sheet2!$A1:$A3)*(A2>=Sheet2!$A1:$A3),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**cyberkidkiller** · 03-19-2014, 09:46 AM

That worked perfectly.

Thanks for your help Biff looks like owe you a beer!!

**Tony Valko** · 03-19-2014, 09:48 AM

I'll have St. Pauli Girl dark.

You're welcome. Thanks for the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.