I need to look for a number and return the column heading.

[Don M.]

I have a large matrix of values, left column goes from 40 to 192 by 2's and
the header row goes from 5 to 50 by 1's, but for my question I will use a
small matrix. The table value is just the row header multiplied by the column
header of that cell.

5 6 7 8 9 10 11
40 200 240 280 320 360 400 440
42 210 252 294 336 378 420 462
44 220 264 308 352 396 440 484
46 230 276 322 368 414 460
48 240 288 336 384 432 480
50 250 300 350 400 450 500
52 260 312 364 416 468
54 270 324 378 432 486
56 280 336 392 448
58 290 348 406 464

From week to week I have a different value to look for in the first column.
Say this week that I need to look in the row with 52 in the left column. I
need a formula, or macro, that looks for the value closest to but not greater
than 500 and then returns the column header value of that cell. In this case
it would be 9. Next week I might need to look for 58 and I need to return 8.
The criteria of 500 could change so I need that to reference from a cell with
500 in it.

I thought of a GETPIVOTDATA function but I don't think that's going to do
what I need. I don't have a good grasp of all of the advanced LOOKUP
functions to just go right to it.

Any help would be appreciated.

Don M.

 

[Don M.]

I just tried to use a variation of VLOOKUP with a formula in the
Col_index_num line but it won't let me use a formula there.

something like =VLOOKUP(A20,A1:H11,max()/A8,false). There must be function
that can look in a table like this I just don't see it in the function list.

Don M.

 

[Mike H.]

This will work:


Sub FindVal()
Dim X As Long
Dim Y As Long
Dim Z As Long
Dim W As Long

Let X = Cells(3, 3).Value 'this is a cell where I record the Row #
Let Y = Cells(4, 3).Value 'this is the cell where I record what I'm looking
for
'in your example x=52 and Y=500
'In my code below the column "headings" are in row 10. If yours is
different, adjust the 10 in the msgbox below!

Z = 1
Do While True
If Cells(Z, 1).Value = X Then Exit Do
Z = Z + 1
Loop
'now z is the row that contains the # (52 in your example)
W = 1
Do While True
If Cells(Z, W).Value <= Y And Cells(Z, W + 1).Value > Y Then
Cells(Z, W).Select
MsgBox ("Your answer is Row " & Z & " and column " & W & "Where the
Column Heading is " & Cells(10, W).Value)
Exit Sub
End If
W = W + 1
Loop



End Sub

 

[Don M.]

Thanks Mike, it took me a few passes to understand how your code works. I
think that'll get it done for me. Very clever.

Thank you!

Can you think of a formula that can be placed in the cell where I want this
value output if I have trouble using a macro to figure this out. I know I'll
be able to use it, it's the other people that have to use the workbook that
I've got to be careful not to go over their heads. If I had a function in
that cell, it may keep it simplified enough to do that.

Don M.

 

[Don M.]

One more little request. I'd like this macro to run without all of the sheet
switching back and forth. I start out on the sheet called Run Report and I'd
like the screen to stay there while all of these loopig is going on. Would I
use the With/End With to do this? How would I change the code then to make
sure the macro is searching the correct data?

Here's what I've got working now:

Sub FindBundleSize()
Sheets("Run Report").Select

Dim X As Long, Y As Integer, Z As Long, W As Long

Let X = Cells(11, 3).Value 'Cell with Number of Pages in it
Let Y = 2600 'Max Pages per bundle

Sheets("Books Per Bundle").Select 'Sheet with the data table in it

Z = 2 'First row of the table with
page sizes in it.
'Range("A1").Select
Do While True
If Cells(Z, 1).Value = X Then Exit Do
Z = Z + 1
Loop 'Z = row number with page count in it

W = 2 'First column to start looking for 2600 in Row Z

Do While True
If Cells(Z, W).Value <= Y And Cells(Z, W + 1).Value > Y Then
BooksPerBundle = Cells(1, W)
Sheets("Run Report").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = BooksPerBundle
Exit Sub
End If
W = W + 1
Loop

End Sub

 

[Don Guillett]

Try this
Sub FindBundleSize()
'Sheets("Run Report").Select

Dim X As Long, Y As Integer, Z As Long, W As Long

Let X = Cells(11, 3).Value 'Cell with Number of Pages in it
Let Y = 2600 'Max Pages per bundle

With Sheets("Books Per Bundle") 'Sheet with the data table in it
Z = 2 'First row of the table with
page sizes in it.
'Range("A1").Select
Do While True
If .Cells(Z, 1).Value = X Then Exit Do
Z = Z + 1
Loop 'Z = row number with page count in it

W = 2 'First column to start looking for 2600 in Row Z

Do While True
If .Cells(Z, W).Value <= Y And .Cells(Z, W + 1).Value > Y Then
BooksPerBundle = .Cells(1, W)
'Sheets("Run Report").Select
'Range("C2").Select
'======
'set a range here
ActiveCell.FormulaR1C1 = BooksPerBundle
'=======
Exit Sub
End If
W = W + 1
Loop
End With
End Sub

 

[Rick Rothstein]

Is the chart really structured like you showed us; that is, is the A2 value
really 200 and are the subsequent values really just the header values added
to the previous entry? If so, your solution is nothing more that this...

=INT(BB1/BA1)

where BA1 contains your lookup value of 52 and BB1 contains your 500 maximum
lookup value. That is, in numerical terms, INT(500/52). Notice, this is a
purely mathematical representation of your chart... the actual chart is not
required (that is, it does not need to physically exist) in order to produce
the result you are looking for. If your chart is structured differently than
you have shown, then tell us what that structure is and perhaps a purely
mathematical solution will still exist for it.