xlookup lookup_area

[- JJK -]

Hi
Is there a function like xlookup which looks the value from area instead of array?

My problem description, I have SAP export as a data file. I would like to find a cost for a certain item. Item ID in export can be in columns E to M and cost is in column N always.

Normal excel function syntax is:
=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode)
this lookup array can refer to 1 column only (lookup_array), either E or F or G or....

so in my case I would like it to be something like:
=XLOOKUP(lookup_value, lookup_area, return_array, if_not_found, match_mode, search_mode)
I would like to search work over all column from E to M

Is there such a function already or is there a combination of functions which should work in my case?

Thanks in advance,
- JJK -

 

[shamimarita]

Hello JJK,
You can use XLOOKUP with BYROW to search for an Item ID across multiple columns (E to M) and return the corresponding cost from column N.

• Assuming your lookup value is in cell P2, try this formula:

=XLOOKUP(TRUE,BYROW(E2:M1000,LAMBDA(r,OR(r=P2))),N2:N1000,"Not found")

• BYROW checks each row from columns E to M for the Item ID in P2.
• OR returns TRUE if the Item ID appears anywhere in that row.
• XLOOKUP finds the first matching row and returns its cost from column N.

This formula works in Excel 365 and returns the first matching result. If the same Item ID appears in multiple rows and you need all corresponding costs, you can use FILTER instead.

 

[- JJK -]

Hello JJK,
You can use XLOOKUP with BYROW to search for an Item ID across multiple columns (E to M) and return the corresponding cost from column N.

• Assuming your lookup value is in cell P2, try this formula:

=XLOOKUP(TRUE,BYROW(E2:M1000,LAMBDA(r,OR(r=P2))),N2:N1000,"Not found")

• BYROW checks each row from columns E to M for the Item ID in P2.
• OR returns TRUE if the Item ID appears anywhere in that row.
• XLOOKUP finds the first matching row and returns its cost from column N.

This formula works in Excel 365 and returns the first matching result. If the same Item ID appears in multiple rows and you need all corresponding costs, you can use FILTER instead.

Thanks for your answer. First hit did not work, do you know whats wrong here:
"
=XLOOKUP(TRUE;BYROW('[SAP24.09.26.xlsx]hours'!$B$10:$N$57118;LAMBDA(r;OR(r=C11)));'[SAP 24.09.26.xlsx]hours'!$O$10:$O$57118;"Not found";2)
"
All results were "Not found"

Thanks

 

[- JJK -]

Hello JJK,
You can use XLOOKUP with BYROW to search for an Item ID across multiple columns (E to M) and return the corresponding cost from column N.

• Assuming your lookup value is in cell P2, try this formula:

=XLOOKUP(TRUE,BYROW(E2:M1000,LAMBDA(r,OR(r=P2))),N2:N1000,"Not found")

• BYROW checks each row from columns E to M for the Item ID in P2.
• OR returns TRUE if the Item ID appears anywhere in that row.
• XLOOKUP finds the first matching row and returns its cost from column N.

This formula works in Excel 365 and returns the first matching result. If the same Item ID appears in multiple rows and you need all corresponding costs, you can use FILTER instead.

Guten morgen
Strange is that this Mr. BYROW is partially working in another file but I cannot make it work with this SAP export file.
Br,
- JJK -

 

[shamimarita]

Hello JJK,

Good morning, and thanks for the update. If the same BYROW approach works in another workbook but not with the SAP export, the issue is likely related to how the SAP data is stored rather than the BYROW function itself.

SAP exports often contain values stored as text, extra spaces, or hidden characters. Because of that, a value that looks identical to C11 may not actually be an exact match.

You can test this first with a simpler formula such as:
=COUNTIF('[SAP 24.09.26.xlsx]hours'!$B$10:$N$57118;C11)

If this returns 0 even though you can see the value in the SAP data, then the lookup values are probably stored differently.

You can also try cleaning the comparison inside BYROW:
=XLOOKUP(TRUE;BYROW('[SAP 24.09.26.xlsx]hours'!$B$10:$N$57118;LAMBDA(r;SUM(--(TRIM(r&"")=TRIM(C11&"")))>0));'[SAP 24.09.26.xlsx]hours'!$O$10:$O$57118;"Not found";0)
The &"" converts the values to text, and TRIM removes unnecessary spaces before comparing them.

Please try the COUNTIF test first. That will help confirm whether the issue comes from the SAP-exported data format.

 

[garfieldandew]

Hi
Is there a function like xlookup which looks the value from area instead of array?

My problem description, I have SAP export as a data file. I would like to find a cost for a certain item. Item ID in export can be in columns E to M and cost is in column N always.

Normal excel function syntax is:
=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode)
this lookup array can refer to 1 column only (lookup_array), either E or F or G or....

so in my case I would like it to be something like:
=XLOOKUP(lookup_value, lookup_area, return_array, if_not_found, match_mode, search_mode)
I would like to search work over all column from E to M

Is there such a function already or is there a combination of functions which should work in my case?

Thanks in advance,
- JJK -

Yes, you can do this with a dynamic-array approach. For example, =XLOOKUP(A2,TOCOL(E2:M100),TOCOL(N2:N100), "Not found") can search all the values in E, but the return range needs to be aligned with the corresponding rows. If you're using Microsoft 365, TOCOL makes this much easier because it converts the area into a single lookup column.

 

[williamcorlin]

Hi
Is there a function like xlookup which looks the value from area instead of array?

My problem description, I have SAP export as a data file. I would like to find a cost for a certain item. Item ID in export can be in columns E to M and cost is in column N always.

Normal excel function syntax is:
=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode)
this lookup array can refer to 1 column only (lookup_array), either E or F or G or....

so in my case I would like it to be something like:
=XLOOKUP(lookup_value, lookup_area, return_array, if_not_found, match_mode, search_mode)
I would like to search work over all column from E to M

Is there such a function already or is there a combination of functions which should work in my case?

Thanks in advance,
- JJK - hot games

Hi JJK, you can handle this with XLOOKUP by combining the lookup columns into one array. For example, if your data is in rows 2:1000, you could use:

=XLOOKUP(lookup_value,TOCOL(E2:M1000,1),TOCOL(N2:N1000,,1))

However, there is an important detail here: TOCOL(E2:M1000) creates a list column-by-column, while the cost in column N needs to stay associated with the correct row. A safer approach is to use FILTER with a row-wise test, for example:

=XLOOKUP(TRUE,BYROW(E2:M1000,LAMBDA(r,COUNTIF(r,lookup_value)>0)),N2:N1000,"Not found")

This searches each row across columns E and returns the corresponding value from column N. If the same item ID can appear more than once, you'll also want to decide whether you need the first or last matching cost.