Share via

Lookup and reference data to another table

Anonymous
2024-11-26T03:49:41+00:00

Hi, I am trying to auto reference information in a table from data generated in another table, this is for a table seating arrangement for an event, where I would plan all the guests' seating arrangement, and hope to auto-reference these information to another sheet which is the attendance taking sheet, where the list of guests would have their table number automatically referenced from the seating arrangement sheet to ensure accuracy.

I have two sheets.

Sheet 1: A table showing all the seating arrangement for guests;

Sheet 2: A list showing attendance taking sheet of all guests, and their table number.

It seems XLOOKUP doesn't work, as for XLOOKUP it is not possible to search in a horizontal array and return values from a vertical array.

All guests' names have been made to match the information on all sheets to ensure correct referencing.

Can anyone advice please?

Thank you.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2024-11-26T07:42:24+00:00

You are welcome:) You may also give feedback on it.

Was this answer helpful?

1 person found this answer helpful.
0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-11-26T07:58:20+00:00
    1. Sheet1!$E$5:$K$16=M6:
      • This part checks if each cell in the range E5:K16 on Sheet1 is equal to the value in cell M6.
      • It creates an array of TRUE and FALSE values. TRUE where the cell matches M6, and FALSE where it doesn't.
    2. --(Sheet1!$E$5:$K$16=M6):
      • The double minus (--) converts the TRUE and FALSE values into 1 and 0 respectively. This is known as a double unary operator.
      • So, TRUE becomes 1 and FALSE becomes 0.
    3. BYCOL(--(Sheet1!$E$5:$K$16=M6), LAMBDA(a, SUM(a))):
      • BYCOL applies a function to each column of the array.
      • LAMBDA(a, SUM(a)) is a custom function that sums the values in each column.
      • This means it sums the 1s and 0s in each column, resulting in an array where each element is the sum of matches in that column.
    4. BYCOL(--(Sheet1!$E$5:$K$16=M6), LAMBDA(a, SUM(a))) > 0:
      • This checks if the sum of matches in each column is greater than 0.
      • It creates an array of TRUE and FALSE values. TRUE where there is at least one match in the column, and FALSE where there are no matches.
    5. XLOOKUP(TRUE, ..., Sheet1!$E$4:$K$4):
      • XLOOKUP searches for the first TRUE in the array created by the previous step.
      • When it finds TRUE, it returns the corresponding value from the range E4:K4 on Sheet1.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2024-11-26T07:49:26+00:00

    Just did, thank you again.

    Would you mind explaining in detail how this works like teaching a school student? This works really well and I'm trying to understand what each of the little bit of the formula does and mean.

    Thank you again for your wonderful help.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2024-11-26T07:08:13+00:00

    Wow! That worked a treat, thank you very much indeed!

    Wish you a good day.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2024-11-26T06:09:13+00:00

    Try this one

    =XLOOKUP(TRUE,BYCOL(--(Sheet1!$E$5:$K$16=M6),LAMBDA(a,SUM(a)))>0,Sheet1!$E$4:$K$4)

    Was this answer helpful?

    0 comments