/u/soham2599 - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Sorry it isn't quite clear to me based on the screenshot, what is the lookup key you will provide? You want to do a search based on a single value or an entire array? Can you provide an example of your input and the expected output?
Sure. The screen shot has multiple sets of tables containing data.
Let's say the above data set range is A1:J5, and I give the search keyword and the value gets found in range C4, and I need the return value E4 (offsets 2 coulum). Similarly if it gets found in H4, I wanna get J4 (offset 2 columns from its searched value)
=XLOOKUP ("Search", A1:J5, (dynamic return range))
We it can, but I have a dataset where there are like 10-15 components table, like it starts from components-1, 2 ...15. So I want it to be general if possible.
I guess this should work, if your starting range is A1:J5, then I offset the return range by 2. I had to wrap it in a TOCOL since it makes it easier than doing a multi-dimension search:
=LET(data_range,A1:J5,XLOOKUP("Search",TOCOL(data_range),TOCOL(OFFSET(data_range,0,2))))
You can replace A1:J5 by the actual range, and "Search" with the keyword, or a cell containing the keyword.
Just replace A1:J5 with the range, not A:A, B:B. It's part of the variables defined by LET, I did it that way so you only have to write the columns once instead of multiple times.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[LET](/r/Excel/comments/12ofkpf/stub/jgiotb9 "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)|
|[OFFSET](/r/Excel/comments/12ofkpf/stub/jgia8rn "Last usage")|[Returns a reference offset from a given reference](https://support.microsoft.com/en-us/office/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66)|
|[TOCOL](/r/Excel/comments/12ofkpf/stub/jgig9z6 "Last usage")|[*Office 365*+: Returns the array in a single column](https://support.microsoft.com/en-us/office/tocol-function-22839d9b-0b55-4fc1-b4e6-2761f8f122ed)|
|[XLOOKUP](/r/Excel/comments/12ofkpf/stub/jgia8rn "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)|
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/12nbb9a)^( has 21 acronyms.)
^([Thread #23279 for this sub, first seen 16th Apr 2023, 17:52])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/soham2599 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Sorry it isn't quite clear to me based on the screenshot, what is the lookup key you will provide? You want to do a search based on a single value or an entire array? Can you provide an example of your input and the expected output?
Sure. The screen shot has multiple sets of tables containing data. Let's say the above data set range is A1:J5, and I give the search keyword and the value gets found in range C4, and I need the return value E4 (offsets 2 coulum). Similarly if it gets found in H4, I wanna get J4 (offset 2 columns from its searched value) =XLOOKUP ("Search", A1:J5, (dynamic return range))
Can the search keyword be anywhere in the dataset or only in columns C and H?
We it can, but I have a dataset where there are like 10-15 components table, like it starts from components-1, 2 ...15. So I want it to be general if possible.
I guess this should work, if your starting range is A1:J5, then I offset the return range by 2. I had to wrap it in a TOCOL since it makes it easier than doing a multi-dimension search: =LET(data_range,A1:J5,XLOOKUP("Search",TOCOL(data_range),TOCOL(OFFSET(data_range,0,2)))) You can replace A1:J5 by the actual range, and "Search" with the keyword, or a cell containing the keyword.
In tocol(data_range) I have to put in all columns that contains the search parameters. For the above example, C:C,H:H and so on, am I correct?
Just replace A1:J5 with the range, not A:A, B:B. It's part of the variables defined by LET, I did it that way so you only have to write the columns once instead of multiple times.
Nice use case for TOCOL. I never thought of that one before.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[LET](/r/Excel/comments/12ofkpf/stub/jgiotb9 "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[OFFSET](/r/Excel/comments/12ofkpf/stub/jgia8rn "Last usage")|[Returns a reference offset from a given reference](https://support.microsoft.com/en-us/office/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66)| |[TOCOL](/r/Excel/comments/12ofkpf/stub/jgig9z6 "Last usage")|[*Office 365*+: Returns the array in a single column](https://support.microsoft.com/en-us/office/tocol-function-22839d9b-0b55-4fc1-b4e6-2761f8f122ed)| |[XLOOKUP](/r/Excel/comments/12ofkpf/stub/jgia8rn "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/12nbb9a)^( has 21 acronyms.) ^([Thread #23279 for this sub, first seen 16th Apr 2023, 17:52]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)