Hi,
I hoping to get help with a formula which will look for a value in a group of cells,
If the value is found then the group name is returned.
If the value is not found then the next group of cells is searched, and if found in that group then the name of that group is returned.
If not found in that group then a third group is searched and so on.
Please see the example spread sheet attached.
Thank you.
N9:
This is an array formula, confirmed with Ctrl+Shift+Enter.
Please Login or Register to view this content.
Then copy downwards
Last edited by daffodil11; 03-13-2014 at 06:10 PM.
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
Hi daffodil11,
thank you for your formula, it works perfectly. Really appreciate your help.
I noticed the formula also works if I don't enter it as an array formula. Is it good practice to enter it as an array formula anyway?
Best regards,
Peter
Actually, it's totally way better to not do it as an array if you don't have it.
Array formulas eat up valuable processor power, so when it can be avoided it's always best to do so.
Hi daffodil11,
Thanks for taking the time to answer my question, so much to learn.
really appreciate your help.
Pete
No problem. Come back again!
Hi daffodil11,
sorry to trouble you, when I try to use the formula in my bigger sheet I don't seem to be able to get it to work.
I tried both the array and non array forms.
Have attached another spread sheet.
Regards,
Pete
Let's approach this from a different angle:
What is this? Does the data format matter? This puzzle would be considerably easier in another format.
do you mean the cell format? I don't think that will matter.
If you mean the way the data is set out - I download the columns of data in that format. I can probably do something to change it before sorting.
Ok, Hulk Smash. I've been at work for over 12 hours so here's the ugly version because my brain is starting to shut down:
By definition, VLOOKUP won't function on noncontiguous ranges so instead we chain some together. If it doesn't find it, we check the next, then the next, and so on. You can even make all the ranges match if you wanted, such as ??9:??5000.Please Login or Register to view this content.
Last edited by daffodil11; 03-13-2014 at 08:00 PM.
the ugly version looks as sweet as honey to me! works perfectly - thank so much.
Good times, glad I could help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks