XLOOK-UP greater than

[Robert1290]

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 actual age, say 40.

2) I would like to combine the formulas in columns G & H, so that it will work out from a date of birth someones age, but rounded down, like an age in the real world.

Many thanks in advance.

[#2]

@Robert1290 With regard to point 2:

=ROUNDDOWN(((TODAY()-I5))/365.25,0)

You take the formula from H5, without the = sign, and place it in the ROUNDDOWN function in stead of H5.

Not sure what you want in point 1.

[#3]

@ Riny_van_Eekelen thank you, that is so obvious now you have said it, and will help me in the future with other formulas.

For point 1, say if the actual age is more than 40, then I would like to return, say, their name and reference on to a new worksheet.

[#4]

@Robert1290 

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!

[#5]

@Robert1290 If your Excel version supports it you can use the FILTER function. Otherwise, a simple pivot table could do it. Both demonstrated in the attached file on Sheet2

[#6]

@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.

[#7]

@Robert1290 

Here is it with the comments. The pivot table solution is okay too but if you prefer formulas either way is okay.

[#8]

@ 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.