Find a value column header table

[drom]

Hi and thanks in advance!

I know how to do this using VBA
I am trying to have this done using a formula

C8:G9 is a table
The header of the table keeps the string despite I am putting values
So when I have to find a value within the header and the desired value is not a perfect match

How can I get get C5:E6 ??

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2		This Eg is just for my Query
3
4		Lookup Value	5	56	61
5		Desired result	1	34	65	This is What I am expecting using a formula
6		Desired Colimn Answer	2	4	5	This is What I am expecting using a formula
7
8			YEAR	1	15	34	65			I can Add more years to my column (eg in H8)
9
10
11
12
13
Sheet1

 

[Peter_SSs]

In words, what is the logic for the "Desired result" values?

C8:G9 is a table

What is the name of that table?

 

[drom]

In words, what is the logic for the "Desired result" values?


What is the name of that table?

The name of my table is Table1

 

[Peter_SSs]

The name of my table is Table1

Thanks. That answers my second question, but not the first.

All I could guess was:
Lookup value of 5 is not found in the table so return the next lowest value that is found in the table (=1)
Lookup value of 56 is not found in the table so return the next lowest value that is found in the table (=34)
Lookup value of 61 is not found in the table so return the next lowest value that is found in the table (=34)

Obviously my guessed logic is incorrect because you say the 3rd result should be 65, not 34. That's why I am asking for the logic to be explained.

 

[drom]

I have to use a vlookup, I know how to find a Sting in a column
But since tables headers are strings
How can I find the column number using a non exact match number value
I can use above the table the numbers but the table may grow in column numbers
And I would like to know if there is any other way

Thanks

 

[Peter_SSs]

How can I find the column number using a non exact match number value

You still have not explained how your non-exact match is supposed to work. As I explained in my last post for two of your non-exact matches your desired result is a lower number, but for the other non-exact match your desired result is higher number.

 

[drom]

• B5:B6 shows my questions
• If I put C4:E4 by hand
  • I would like to get using a formula C5:E5
• If I put C4:E4 by hand
  • I would like to get using a formula C6:E6 ( a different formula)

Green Cells are expecting a formula, I do not know hot to get this done


Row 4 are my Targets,
Row 5: C5:E5 I need a formula
Row 6: C6:E6 I need a different formula tha the one I need for row 5
I Row 5 and 6 in green color are the formulas I need (two different formulas, one for each row)

THANKS

 

[Peter_SSs]

OK, since you seem to be refusing to give any logic behind the answers that you want to get, I'll make up some of my own.
This gives the results that you asked for.

drom.xlsm
	B	C	D	E	F	G
4	Lookup Value	5	56	61
5	Desired result	1	34	65
6	Desired Colimn Answer	2	4	5
7
8		YEAR	1	15	34	65
9
Sheet1

Cell Formulas
Range	Formula
C5:E5	C5	=XLOOKUP(C4:E4,--Table1[#Headers],--Table1[#Headers],,IF(C4:E4<60,-1,1))
C6:E6	C6	=MATCH(C5#&"",Table1[#Headers],0)
Dynamic array formulas.