Hello Guys,
Hope all of you are OK.
In excel file attachment have a very simple question: - using Indirect Function + Xlookup + 2 Criteria.
The cell reference is B3.
Already have table named ranges "JAN" and "FEB".
Table results is in B5:E8.
Can you please give a hand?
Thank you very much!!!
For the sample data:Formula:
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I would try to avoid INDIRECT, id possible. If
a) your O365 is a recent version, and
b) the "bricks" are in the same order (as they were in your sample), why not use a one-cell dynamic array:
=LET(A,SCAN("",H4:P4,LAMBDA(x,y,IF(y="",x,y))),CHOOSECOLS(FILTER(H6:P8,(A=B3)),2,3,4))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
And if they are in a different order int he result section, you can use:
=LET(A,SCAN("",H4:P4,LAMBDA(x,y,IF(y="",x,y))),B,B6:B8,C,FILTER(H6:P8,(A=B3)),DROP(REDUCE(0,B,LAMBDA(x,y,VSTACK(x,FILTER(C,INDEX(C,,1)=y)))),1,1))
Dear Guys,
Thank you very much to all.
Thats a small example.
In my personal workbook have a 12 different tables each one with a lot of columns and rows.
Dear Glenn Kennedy,
Yes I have O365 in this particular formula would be more usel if use "Indirect" with XLookup because it would be more easier to relate each one individually.
Can you please give a hand?
Thank you very much. Indirect Function + Xlookup_V2.xlsx
I'm on my way out to a big gig in Dublin. I'll take a look in the morning.
Dear Glenn Kennedy,
Sure my friend!!!
Have a great time!!!👍👍🍻🍻🍻
Big hug.
Are you saying that the header columns (JAN, FEB) that were in your sample sheet are NOT in your real data??
If so:
=CHOOSE(MONTH(1&B3),I6:K8,N6:P8)
and add on additional months:
=CHOOSE(MONTH(1&B3),I6:K8,N6:P8,March,April,May,etc)
Dear Gleen Kennedy,
Hope are fine.
In order to help just made an upgrade of the tables structures more similar to my personal workbook.
When change cell B3 to "JAN" it is correct but when change to "FEB" give an error and doesnt the correct values.
Without disturb can you please give an expertise hand?
Thank you very much.Indirect Function + Xlookup_V2.xlsx
It is OK. Change it to Jan, then back to Feb again....
Double-check that calculation options are set to automatic.
Dear Gleen Kennedy,
Highly grateful.
Just decide to put my workbook structure at your disposal so you can expertisely give me your feedback.
Have only the tables month with all data: Jan, Feb. In this example to be simple just put 2 months however in my personal will have the 12 months.
With the refrence month in cell in C3 and reference D7 want to fill data in range C9:J9 accordingly.
Thats why my question about indirect function would be more easier because of the amount of tables and data.
Can you give a look? Indirect Function + Xlookup_V3.xlsx
Any question please let me know.
Thank you very much Gleen.
1. Where is January? Please put in AT LEAST two months.
2. What is meant to go in column K?
3. Is this text "hmR Region Values" always in the first column/row in each month?
Dear Gleen Kennedy,
Highly grateful.
1.So, Jan is data in range M44:AE78 and FEB range M7:AE41.
2.In order to help just gave in sheet 2 different examples accordingly what it has to go in column C8:J9.
3.Yes.
Indirect Function + Xlookup_V3.xlsx
Any question please let me know.
Thank you very much.
Before spending more time on this....
Originally, you showed February BESIDE January... now you show it BELOW January. Where are the remaining months? Nicely organised below January and February... or in some horrible 2D pattern.
Last edited by Glenn Kennedy; 03-17-2023 at 11:05 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks