Hi,
I think I need an index match formula for my problem but I cant get my head around it.
Hopefully someone can help me out.
I want to populate L6 with a formula in the race sheet with the the corresponding time in the course sheet, then copy it down to fill the rest of L.
The answer would be 95.8 which is SANL/1600/Good on the race sheet.
IM.jpg
IM1.jpg
May you post the workbook instead of the pictures?
Thank,
Mohamed GadAllah
Give me a couple of minutes to get my formulas etc out of it.
I can also change how the course sheet is set out if that helps.
Am having trouble figuring out how to attach it.
I uploaded it in manage attachments ok.
Last edited by Doc_62; 03-01-2024 at 04:08 AM.
Well blow me down, it worked lol
one way, remeber to commit with Ctrl+Shift+Enter after input.
copied down.
Please Login or Register to view this content.
Do you still have Excel 2010?
If you have upgraded to 2021 or 365, you could use this:
=FILTER(FILTER('Course '!$D$5:$F$32,('Course '!$A$5:$A$32=C6)*('Course '!$B$5:$B$32=E6)),'Course '!$D$3:$F$3=F6)
If not, you can use this:
=SUMPRODUCT(('Course '!$A$5:$A$32=C6)*('Course '!$B$5:$B$32=E6)*('Course '!$D$3:$F$3=F6),'Course '!$D$5:$F$32)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Yeah, I cant get that into a worksheet. Whats the Ctrl+Sfift+Enter thingy?
Have a look at my suggestions in post #7 - they don't need to be entered with CSE. You should be able to open any workbook here.
Are you still using Excel 2010?
Hold down both the CTRL and SHIFT keys (normally on the bottom left of a keyboard) and press ENTER.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
The second formula worked a charm!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Thank you ever so much!
You just saved me many many hours of work.
Glad to have helped.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, remember that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Uuuummm....... I've broken it already
How do I mark the thread as not solved?
So I went to a new race and started adding more courses to the course sheet.
Now it wont return anything
My fault.
Maybe I should have mentioned that is going to be variable.
At least a hundred courses and hundreds of horses.
I have attached the new file I started
Are you still using 2010? No further assistance until you answer this question ...
Its Excel 2016
Right - update your forum profile NOW, please.
If you change the layout of the lookup data, which you did, then you need to change the ranges to match:
=SUMPRODUCT(('Course '!$A$4:$A$500=C6)*('Course '!$B$4:$B$500=E6)*('Course '!$D$2:$F$2=F6),'Course '!$D$4:$F$500)
Last edited by AliGW; 03-01-2024 at 04:30 AM. Reason: Workbook added.
I will be adding a lot more as I said, but I should be able to follow along with the changes you have made.
Thank you!
Just trying to get dinner organised so I cant look at it for half an hour or so
Great - you can make the range as long as you like - I set it at 500, but it could be 5000 or more.
Thanks for updating your profile.
=SUMPRODUCT(('Course '!$A$4:$A$5000=C6)*('Course '!$B$4:$B$5000=E6)*('Course '!$D$2:$F$2=F6),'Course '!$D$4:$F$5000)
I'm guessing that 500 number is the key.
It cant be that simple can it?
The courses will only be expanding down so I hope that does the trick.
So Far so good!
The zero's are courses I haven't added yet.
The M column is using a formula to give me a speed rating for the horse.
IM 2.jpg
Last edited by Doc_62; 03-01-2024 at 05:09 AM.
Yes, of course it's that simple as long as you don't change the layout again.
This is weird.
Why is it picking up most of the times but not all?
I'd like to understand it so I can sort out any problems in the future.
Everything looks sweet.
The first fail is on row 25
There are some times missing in course which is fair enough. It just means I have to manually enter them. A lot are there though.
Spot the problem:
AliGW on MS365 Beta Channel (Windows 11) 64 bit
ASCT Good Slow Heav
Sheet: Course
You are kidding me!
So a space between the course condition is fine everywhere but with the first course it isnt????
Last edited by Doc_62; 03-01-2024 at 07:37 AM.
It's nothing to do with a space. Where did you get that idea? It's to do with that heading being wrong. If it's wrong, there's no match.
Soft <> slow!
AliGW on MS365 Beta Channel (Windows 11) 64 bit
7-12 M V Bench. 100 Soft 1-The Summit
Sheet: Race
Last edited by AliGW; 03-01-2024 at 07:38 AM.
I deleted the space and bingo! It all came together.
It had nothing to do with a space. See the file attached to post #24.
But if you are happy to troubleshoot yourself and ignore my explanations, then that's fine.
Nope, thats on your sheet.
Sorry, still dont see the mistake
You are now making no sense - what's not on my sheet??? All I changed in the file attached to post #24 was the incorrect heading in the source data. Take a moment to read my posts, please, otherwise I am wasting my time trying to get you to understand.
Doh!!!!!!!!!!!!!!!
And that threw every soft rating after it out?
Dang.
I feel much better now I understand it.
How did I miss that???
Your a champ!
Every soft was wrong because the first entry was slow.
Partly by not taking a breath and reading what I said!!! But at least you now understand.How did I miss that???
Yes, of course it would throw every soft rating out because it is the headers in row 2 that the formula is using.
Glad to have helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks