XLOOKUP Help

[tim220225]

Hello, I need some help with XLOOKUP. I am tasked with making this worksheet pull data that corresponds with the number of divisions in cell (G3). I have tried multiple ideas and can't get the results I need. I now have it partially working up to 43 divisions as in the image attached. I need to adjust my current formula to return the first three columns to the right of the number of divisions higher than 43. For the life of me I can't get it to work. What is in yellow is how I need it to display.

Thanks for the help!

 

[keef2]

Can you upload with Xl2BB? Its a very nice tool and much easier to see your data and current formulas. Also you should update your profile to incorporate what version of excel you are using.

 

[fjns]

Hi, try this formula
=IFERROR(XLOOKUP(G3,A6:A47,B6:D47),IFERROR(XLOOKUP(G3,E6:E47,F6:H47),IFERROR(XLOOKUP(G3,I6:I47,J6:L47),XLOOKUP(G3,M6:M47,N6:P47))))

 

[tim220225]

Can you upload with Xl2BB? Its a very nice tool and much easier to see your data and current formulas. Also you should update your profile to incorporate what version of excel you are using.

I have tried to install this tool. I’m going to have to work on it more.

 

[tim220225]

Hi, try this formula
=IFERROR(XLOOKUP(G3,A6:A47,B6:D47),IFERROR(XLOOKUP(G3,E6:E47,F6:H47),IFERROR(XLOOKUP(G3,I6:I47,J6:L47),XLOOKUP(G3,M6:M47,N6:P47))))

That solution works! I was using a similar formula without the IFERROR. I need to work with this formula to understand how the addition of IFERROR made it work.

Thank you very much!

 

[Fluff]

As Xlookup has it's own built error handling, you don't need iferror, you can use

=XLOOKUP(G3,A6:A47,B6:D47,XLOOKUP(G3,E6:E47,F6:H47,XLOOKUP(G3,I6:I47,J6:L47,XLOOKUP(G3,M6:M47,N6:P47,""))))

 

[tim220225]

As Xlookup has it's own built error handling, you don't need iferror, you can use

=XLOOKUP(G3,A6:A47,B6:D47,XLOOKUP(G3,E6:E47,F6:H47,XLOOKUP(G3,I6:I47,J6:L47,XLOOKUP(G3,M6:M47,N6:P47,""))))

Thank you! This works and is cleaner.

 

[Fluff]

Glad we could help & thanks for the feedback.