Return a column of values based on 2 way search

Ref the attachment. Using an xml feed button a column of values with a count between 4 and 6 is returned at Col A Range A2:A7. There is a count of the values at cell D4. I need a formula at B2 to look up the three tables for columns with either 4, 5 or 6 values, then H lookup within the appropriate table the parameter column that matches the value of the cell at A2.

From the example: the count is '4' values, so the table we're interested in is A11:D16. The value of A2 is '2.2' so the column with the label '>2 to 3' is the correct column. Therefore the values in the range C13:C16 are returned to B2.

I could solve this problem if I wanted to return a single value as a result of 2 criteria, but not sure how to return a column of values.

Much appreciate your interest.

hi..

please check..

regards,

Not quite sir. If you type '1' followed by a set of 3 following numbers down the column, it returns the Range B13:B16. That column is for numbers >1. It should return the Range A13:A16.

But you've nearly solved it, well done!

ups...
sorry..

please change formula on B2 as below.
"IFERROR(INDEX((OFFSET($A$13,0,($D$4-4)*4):OFFSET($A$13,3+($D$4-4),3+(($D$4-4)*4))),ROW()-1,MATCH(ROUNDUP($A$2,0),{1,2,3,4},0)),"")"
and copied down.

regards,

I'm getting an error message now, I've rechecked that the formula is verbatim 3 times. Would you mind editing the spreadsheet with the new amendment and resending.
Cheers

please check.

Sweet as! Thankyou so much. In your previous post you had an '*' before MATCH, which was causing the problem.

Cheers again.

Originally Posted by JR DHONA

please check.

To JR Dhona or anyone else interested,

You did well with that simplified sample I provided, but I am having trouble interpreting your formula to apply to a real world exercise. I have attached a smaller version of my current workbook. There are some changes that affect the way your solution worked. Namely:
1/ The first column of each Count # table varies from <=1.5 for Counts 6 & 7, then increases to <=2 for Count 8
2/Similarly the Max column label is >3 for Counts 6 & 7, but increases to >3.5 for Count 8
3/The syntax <=3 or >3 has to be followed exactly and no value "Rounded"
4/Each Count # table always comprises 5 column headings. In reality the Count # tables start at 6 and go through to 10 with 5 columns to each table, a total of 25 columns.

Thanking you for your consideration.

I was able to solve my own conundrum.
I just needed a nested IF AND formula to categorize the Parameter. This gave me two distinct criteria to MATCH
Then inverted the Table so the offset worked on columns, not rows.
Renamed the Parameters so they couldn't be returned as text in the IF AND formula.

Works a treat now!

dear peasther,

sorry just reply..
thank you for sharing.
for some case,
we learn that change the layout of data is the best solutions.

regards,