Need to improve formula or VBA.

[Russk68]

Hi all!

I am using this formula (below) to return a result based on a number in the example below. Sheet1, Column A is an ID start number and B is an item.


Sheet 1 data entry
A...............B
1............Apple
50..........Banana
201........Orange

Sheet 2, column A is where I enter a number to get a result in column B. Any number between 1 and 49 will result Apple. a number 50:200 will result Banana and so on.
Sheet 2, column B formula =IFERROR(INDEX(Instruments!$AX$3:$AX$40,MATCH('Patch By UNIVERSE'!A5,Instruments!$A$3:$A$40,1)),"")

Example result sheet 2:

A...............B
3.............Apple
17...........Apple
57...........Banana
199.........Banana
200.........Banana
201.........Orange
378.........Orange

This formula works as long as the ID start numbers in sheet1, column A, are in ascending order. I would like to enter start IDs in any order to get the desired result.

Can a formula do this or is VBA the way to go?

Thank you!

 

[Rick Rothstein]

This formula works as long as the ID start numbers in sheet1, column A, are in ascending order. I would like to enter start IDs in any order to get the desired result.

Can't you enter your start ID's and their description in any order and then immediately sort those two columns in ascending order keyed to the ID column?

 

[kweaver]

I think the only formula alternative (that I could come up with quickly) is to have column A be the low end of the range and column B be the high end, with your results in column C.

Then, on your Sheet2 in the B column, this should work:

=INDEX(Sheet1!$C$1:$C$3,SUMPRODUCT((A1>=Sheet1!$A$1:$A$3)*(A1<=Sheet1!$B$1:$B$3)*ROW($1:$3)),1)

 

[Russk68]

Thank you both for options that I haven't considered.