Search and retrieve a value based on a criteria that sits across multiple different rows?

[jvs411]

I know of formulas like XLOOKUP, VLOOKUP and INDEX-MATCH but I've always only used these for retrieving values based on one specific row and one specific column.
What if I want to retrieve a value based on multiple rows/columns though?

Take this simple scenario for example:

There are these five black rows containing codewords and below each black row are white rows of numbers.
However, the codeword I want to search for (eg. A3) may lie in any of these five rows.

How can I write a formula to search as such?
I was thinking of doing something like a nested XLOOKUP, but then it'd be super long. And if possible, I'm hoping for the formula to be dynamic based on the month.
So if I'm searching for the value of criteria code H1 in April, the formula will search for H1 in the row 10, and the two immediate rows below and above (row 7 & row 13)

Is there a simper way to do this?

 

[Micron]

Should be possible to code for it if you're open to that solution. If not, then I'm not much of a formula guy so can't help with that.
If code, you need a trigger. That could be anything from a simple double click on say any cell in a column (or any column) to a userform. Going with double click:
- raise an input box, you type in APR,H1. If user enters a space there (APR, H1) remove it (or just Trim it later)
- code does a Find operation for value before the comma (APR) and gets row 10
- then creates a range object from row - 3, column C to row + 3 to column M (?)
- code does a Find operation over the range looking for the value after the comma (H1)
- returns the value in the cell below (I don't see where you stated what was to be returned by the formula)

I do know Access vba fairly well and dabble in Excel vba and believe the above is doable. All you'd need is the code as a function and type the function call into whatever cell you need the result in. You can drag this down or across the rows or columns as well.

 

[Herakles]

I know of formulas like XLOOKUP, VLOOKUP and INDEX-MATCH but I've always only used these for retrieving values based on one specific row and one specific column.
What if I want to retrieve a value based on multiple rows/columns though?

Take this simple scenario for example:

There are these five black rows containing codewords and below each black row are white rows of numbers.
However, the codeword I want to search for (eg. A3) may lie in any of these five rows.
View attachment 101075

How can I write a formula to search as such?
I was thinking of doing something like a nested XLOOKUP, but then it'd be super long. And if possible, I'm hoping for the formula to be dynamic based on the month.
So if I'm searching for the value of criteria code H1 in April, the formula will search for H1 in the row 10, and the two immediate rows below and above (row 7 & row 13) View attachment 101074

Is there a simper way to do this?

The data needs to be structured in a way that easily and obviously facilitates such a search.

Can you submit your data using XL2BB and I will make a suggestion.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

In the example you show above, where does the 'M14,' value come from and what do you want the formula / code to return and display?