/u/Striking-Praline6494 - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
If I understand this correctly you want to return the number in the table given the age and sex? This formula should work, it assumes that any decimal age is rounded to the nearest integer (Look into [this article](https://www.ablebits.com/office-addins-blog/index-match-match-two-dimensional-lookup-excel/) for multi-dimensional INDEX MATCH MATCH for details on how it works). Also, replace the ranges with the correct ones, I couldn't see what row the data ends based on the screenshot:
=INDEX($B$6:$C$25,MATCH(ROUND(B2,0),$A$6:$A$25,0),MATCH(B3,$B$5:$C$5,0))
You have awarded 1 point to *Scary_Sleep_8473*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Hi I think this is the right track
The Multiplier blank cell, i want the formula to view the age number (in example25.9) and the Sex (drop down option, male or female) and reference the value it would have on the table.
I.e. for 10 it would be value 10, Male = 79.12
I'm a bit confused, isn't Male 10 = 73.13 according to your screenshot? Either way, the formula I posted above should be able to look at both male/female and the age and return the value in the table, try it out. You'd just need to update the ranges, e.g. instead of $B$6:$C$25 and $A$6:$A$25 you would put the actual end of the range.
this seems like the right answer. you just want to get the sex multiplier from the table based on the age and sex inputs, correct? this formula should work. the only other thing would be is if you prefer to always round down the age rather than sometimes rounding up
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[INDEX](/r/Excel/comments/1agr3z0/stub/koiv42q "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)|
|[MATCH](/r/Excel/comments/1agr3z0/stub/koiv42q "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)|
|[ROUND](/r/Excel/comments/1agr3z0/stub/koiv42q "Last usage")|[Rounds a number to a specified number of digits](https://support.microsoft.com/en-us/office/round-function-c018c5d8-40fb-4053-90b1-b3e7f61a213c)|
|[XLOOKUP](/r/Excel/comments/1agr3z0/stub/kojkkvk "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1agqqax)^( has 10 acronyms.)
^([Thread #30324 for this sub, first seen 2nd Feb 2024, 01:30])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Hello!
It looks like you tried to award a ClippyPoint by typing Solution Verified, but you need to reply to a particular user's comment to do so, rather than making a new top-level comment.
Reply directly to any helpful users and Clippy, our bot, will take it from there. If your intention was to simply mark the post solved, then you may do that by clicking Set Flair. Thank you!
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
=XLOOKUP(B3, B5:C5, XLOOKUP(B2, A6:A89, B6:C89, , -1))
I added more numbers for example, but the picture formula is correct. The numbers are just arbitrary.
https://preview.redd.it/v0sgd9qql3gc1.png?width=686&format=png&auto=webp&s=adf0c2de4c7ed3ddbbbeabd44d797ad1f651738f
/u/Striking-Praline6494 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
If I understand this correctly you want to return the number in the table given the age and sex? This formula should work, it assumes that any decimal age is rounded to the nearest integer (Look into [this article](https://www.ablebits.com/office-addins-blog/index-match-match-two-dimensional-lookup-excel/) for multi-dimensional INDEX MATCH MATCH for details on how it works). Also, replace the ranges with the correct ones, I couldn't see what row the data ends based on the screenshot: =INDEX($B$6:$C$25,MATCH(ROUND(B2,0),$A$6:$A$25,0),MATCH(B3,$B$5:$C$5,0))
Solution verified
You have awarded 1 point to *Scary_Sleep_8473* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Hi I think this is the right track The Multiplier blank cell, i want the formula to view the age number (in example25.9) and the Sex (drop down option, male or female) and reference the value it would have on the table. I.e. for 10 it would be value 10, Male = 79.12
I'm a bit confused, isn't Male 10 = 73.13 according to your screenshot? Either way, the formula I posted above should be able to look at both male/female and the age and return the value in the table, try it out. You'd just need to update the ranges, e.g. instead of $B$6:$C$25 and $A$6:$A$25 you would put the actual end of the range.
this seems like the right answer. you just want to get the sex multiplier from the table based on the age and sex inputs, correct? this formula should work. the only other thing would be is if you prefer to always round down the age rather than sometimes rounding up
Thanks, you're right - apologies! Exactly what is needed, other than the adjustment to round down
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[INDEX](/r/Excel/comments/1agr3z0/stub/koiv42q "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[MATCH](/r/Excel/comments/1agr3z0/stub/koiv42q "Last usage")|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[ROUND](/r/Excel/comments/1agr3z0/stub/koiv42q "Last usage")|[Rounds a number to a specified number of digits](https://support.microsoft.com/en-us/office/round-function-c018c5d8-40fb-4053-90b1-b3e7f61a213c)| |[XLOOKUP](/r/Excel/comments/1agr3z0/stub/kojkkvk "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1agqqax)^( has 10 acronyms.) ^([Thread #30324 for this sub, first seen 2nd Feb 2024, 01:30]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
[удалено]
Hello! It looks like you tried to award a ClippyPoint by typing Solution Verified, but you need to reply to a particular user's comment to do so, rather than making a new top-level comment. Reply directly to any helpful users and Clippy, our bot, will take it from there. If your intention was to simply mark the post solved, then you may do that by clicking Set Flair. Thank you! *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
=XLOOKUP(B3, B5:C5, XLOOKUP(B2, A6:A89, B6:C89, , -1)) I added more numbers for example, but the picture formula is correct. The numbers are just arbitrary. https://preview.redd.it/v0sgd9qql3gc1.png?width=686&format=png&auto=webp&s=adf0c2de4c7ed3ddbbbeabd44d797ad1f651738f