Forum Discussion
XLOOK-UP greater than
Hi, I have the attached workbook, and I have 2 problems:- 1) I need to create a lookup (hopefully X) which will return multiple columns, say F & J, if their age is greater than a value in act...
1. I recommend using filter instead of xlookup since you have office 365.
| The formula should be this for returning the name column: |
| =FILTER(Table1[Name],Table1[Actual Age]>=40,"No record") |
| The formula should be this for returning the reference column: |
| =FILTER(Table1[Reference],Table1[Actual Age]>=40,"No record") |
2. Your use of 'rounddown' is correct here, simply replace cell reference with the formula originally in $D3.
Actual Age
| =ROUNDDOWN(((TODAY()-[@[Day of Birth]]))/365.25,0) |
| =ROUNDDOWN(((TODAY()-[@[Day of Birth]]))/365.25,0) |
| =ROUNDDOWN(((TODAY()-[@[Day of Birth]]))/365.25,0) |
| =ROUNDDOWN(((TODAY()-[@[Day of Birth]]))/365.25,0) |
I turned all of it into an official excel table so you'd see [@...] for the references, change it back if you like. If I misunderstood your questions, please let me know. Hope it helps!
Marcus123
Thanks, although I am a little confused, can I see this on the workbook that I have uploaded? Can you include the workbook but amended so I can understand it better? Thanks in advance.
Thanks, although I am a little confused, can I see this on the workbook that I have uploaded? Can you include the workbook but amended so I can understand it better? Thanks in advance.
Here is it with the comments. The pivot table solution is okay too but if you prefer formulas either way is okay.
- @ Riny_van_Eekelen
Marcus123
Thanks both, I didn't see the attached excel before. I will go with formulas as it will stop manual intervention with the workbooks I need it for. This has been very helpful and will allow me to continue my work.