hm...

I need help with a very special formula, that confuses me to no end.

In the attached file you can see the data sheet. This is the source for everything I need on multiple work sheets.



I need to get the ID based on the value of Age in a between format.
For example: if age is between 10080 and 20160 I need to get the ID of that row in work sheet 1

After that I can use simple vlookups to get the rest, but I always get N/A, SPILL or other errors when trying to use IF, AND and VLOOKUP in conjunction.

I hope my meaning is discernible.

Edit: What makes it hard is that the data in the "data" sheet can be quite long (up to 12000 rows) and I want to auto-generate a new list when exchanging the data in that sheet.
I'm not fixed on using vlookup, but I am fixed on avoiding VB.

Edit 2: I can add and change stuff in the "work" sheet. I can even add entirely new sheets if needed. In the end the most important ones will be the sheets discerning between "age" ranges from data. So in effect it should be the same formula only using different numbers.

Kind regards and thanks for the help in advance,
Jack

Attached Files

Test.xlsx (12.0 KB, 7 views)