Unsure on the Subject / Indexing?

A

ajstole

New Member
Joined
Apr 3, 2018
Messages
8
Apologies for not knowing what this may be.

I have the following table and am trying to figure out if excel is even capable of indexing some value. Each number on top has 2 categories. If the desired number chosen is 34, the result should be 30 and 40 for example. If 30 is chosen, the 10 and 20 should show.

[TABLE="width: 498"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD="colspan: 4"]Enter Desired Number Here: [/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD="colspan: 4"]Category 1 last number to be shown:[/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD="colspan: 4"]Category 2 first number to be shown:

[/TD]
[TD="colspan: 2"] [/TD]
[/TR]
[TR]
[TD="colspan: 2"][TABLE="width: 498"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD="colspan: 2"]30[/TD]
[TD="colspan: 2"]32[/TD]
[TD="colspan: 2"]34[/TD]
[/TR]
[TR]
[TD]Category 1[/TD]
[TD]Category 2[/TD]
[TD]Category 1[/TD]
[TD]Category 2[/TD]
[TD]Category 1[/TD]
[TD]Category 2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]20[/TD]
[TD]11[/TD]
[TD]30[/TD]
[TD]21[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]18[/TD]
[TD]12[/TD]
[TD]28[/TD]
[TD]22[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]16[/TD]
[TD]13[/TD]
[TD]26[/TD]
[TD]23[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]14[/TD]
[TD]14[/TD]
[TD]24[/TD]
[TD]24[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]12[/TD]
[TD]15[/TD]
[TD]22[/TD]
[TD]25[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]10[/TD]
[TD]16[/TD]
[TD]20[/TD]
[TD]26[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]8[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]27[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]6[/TD]
[TD]18[/TD]
[TD]16[/TD]
[TD]28[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]4[/TD]
[TD]19[/TD]
[TD]14[/TD]
[TD]29[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]2[/TD]
[TD]20[/TD]
[TD]12[/TD]
[TD]30[/TD]
[TD]22[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
See if this does what you want. If not, please give more explanation/examples.

Excel Workbook
ABCDEF
1Desired Num34
2Cat 1 Last30
3Cat 2 First40
4
5303234
6Category 1Category 2Category 1Category 2Category 1Category 2
712011302140
821812282238
931613262336
1041414242434
1151215222532
1261016202630
137817182728
148618162826
159419142924
1610220123022
Last first
 
Upvote 0
This is awesome!! That was quick, did not expect that at all! If I did add a second column, what about this? Forgive me, I do not how to attach the picture here.

[TABLE="width: 612"]
<colgroup><col span="9"></colgroup><tbody>[TR]
[TD="colspan: 2"]Desired Number[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Cat 1 + Cat 2 Last[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Cat 3 First[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]30[/TD]
[TD="colspan: 3"]32[/TD]
[TD="colspan: 3"]34[/TD]
[/TR]
[TR]
[TD]Cat 1[/TD]
[TD]Cat 2[/TD]
[TD]Cat 3[/TD]
[TD]Cat 1[/TD]
[TD]Cat 2[/TD]
[TD]Cat 3[/TD]
[TD]Cat 1[/TD]
[TD]Cat 2[/TD]
[TD]Cat 3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]11[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]3[/TD]
[TD]27[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12[/TD]
[TD]9[/TD]
[TD]4[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]5[/TD]
[TD]24[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]13[/TD]
[TD]8[/TD]
[TD]6[/TD]
[TD]16[/TD]
[TD]18[/TD]
[TD]7[/TD]
[TD]21[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]14[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]14[/TD]
[TD]17[/TD]
[TD]9[/TD]
[TD]18[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]15[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]12[/TD]
[TD]16[/TD]
[TD]11[/TD]
[TD]15[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]16[/TD]
[TD]5[/TD]
[TD]12[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]13[/TD]
[TD]12[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]17[/TD]
[TD]4[/TD]
[TD]14[/TD]
[TD]8[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]9[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]18[/TD]
[TD]3[/TD]
[TD]16[/TD]
[TD]6[/TD]
[TD]13[/TD]
[TD]17[/TD]
[TD]6[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]19[/TD]
[TD]2[/TD]
[TD]18[/TD]
[TD]4[/TD]
[TD]12[/TD]
[TD]19[/TD]
[TD]3[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]20[/TD]
[TD]1[/TD]
[TD]20[/TD]
[TD]2[/TD]
[TD]11[/TD]
[TD]21[/TD]
[TD]0[/TD]
[TD]21[/TD]
[/TR]
</tbody>[/TABLE]

Peter_SSs said:
See if this does what you want. If not, please give more explanation/examples.

Last first

ABCDEF
Desired Num
Cat 1 Last
Cat 2 First

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:95px;"><col style="width:82px;"><col style="width:82px;"><col style="width:82px;"><col style="width:82px;"><col style="width:82px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]34[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]30[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]40[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]30[/TD]

[TD="align: right"]32[/TD]

[TD="align: right"]34[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]Category 1[/TD]
[TD="align: right"]Category 2[/TD]
[TD="align: right"]Category 1[/TD]
[TD="align: right"]Category 2[/TD]
[TD="align: right"]Category 1[/TD]
[TD="align: right"]Category 2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]40[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]38[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]36[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]34[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]32[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]30[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]28[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]26[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]24[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]22[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
B2=INDEX(A16:F16,MATCH(B1,A5:E5,0))
B3=INDEX(A7:F7,MATCH(B1,A5:E5,0)+1)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...
 
Upvote 0
ajstole said:
This is awesome!! That was quick, did not expect that at all! If I did add a second column, what about this? Forgive me, I do not how to attach the picture here.
Click to expand...

Excel Workbook
ABCDEFGHI
1Desired Num32
2Cat 1 Last20
3Cat 2 First30
4Cat 3 First20
5303234
6Category 1Category 2Category 3Category 1Category 2Category 3Category 1Category 2Category 3
71201130202140
821812282238
931613262336
1041414242434
1151215222532
1261016202630
137817182728
148618162826
159419142924
1610220123022
Last first




BTW
1. Best not to fully quote long posts as it makes the thread harder to read/navigate and just occupies storage space needlessly. If you want to quote, quote small, relevant parts only.
2. Be patient when you submit a post. Especially for new members they can get help up for a while. That should improve as you have been here longer and post more. :)
3. See my signature block below for a link of how to post small screen shots.
 
Last edited:
Upvote 0
Hard to follow...

You have:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][/tr][tr][td]
1​
[/td][td]Enter Desired Number Here:[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
2​
[/td][td]Category 1 last number to be shown:[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
3​
[/td][td]Category 2 first number to be shown:[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
4​
[/td][td]
30​
[/td][td][/td][td]
32​
[/td][td][/td][td]
34​
[/td][td][/td][/tr]
[tr][td]
5​
[/td][td]Category 1[/td][td]Category 2[/td][td]Category 1[/td][td]Category 2[/td][td]Category 1[/td][td]Category 2[/td][/tr]
[tr][td]
6​
[/td][td]
1​
[/td][td]
20​
[/td][td]
11​
[/td][td]
30​
[/td][td]
21​
[/td][td]
40​
[/td][/tr]
[tr][td]
7​
[/td][td]
2​
[/td][td]
18​
[/td][td]
12​
[/td][td]
28​
[/td][td]
22​
[/td][td]
38​
[/td][/tr]
[tr][td]
8​
[/td][td]
3​
[/td][td]
16​
[/td][td]
13​
[/td][td]
26​
[/td][td]
23​
[/td][td]
36​
[/td][/tr]
[tr][td]
9​
[/td][td]
4​
[/td][td]
14​
[/td][td]
14​
[/td][td]
24​
[/td][td]
24​
[/td][td]
34​
[/td][/tr]
[tr][td]
10​
[/td][td]
5​
[/td][td]
12​
[/td][td]
15​
[/td][td]
22​
[/td][td]
25​
[/td][td]
32​
[/td][/tr]
[tr][td]
11​
[/td][td]
6​
[/td][td]
10​
[/td][td]
16​
[/td][td]
20​
[/td][td]
26​
[/td][td]
30​
[/td][/tr]
[tr][td]
12​
[/td][td]
7​
[/td][td]
8​
[/td][td]
17​
[/td][td]
18​
[/td][td]
27​
[/td][td]
28​
[/td][/tr]
[tr][td]
13​
[/td][td]
8​
[/td][td]
6​
[/td][td]
18​
[/td][td]
16​
[/td][td]
28​
[/td][td]
26​
[/td][/tr]
[tr][td]
14​
[/td][td]
9​
[/td][td]
4​
[/td][td]
19​
[/td][td]
14​
[/td][td]
29​
[/td][td]
24​
[/td][/tr]
[tr][td]
15​
[/td][td]
10​
[/td][td]
2​
[/td][td]
20​
[/td][td]
12​
[/td][td]
30​
[/td][td]
22​
[/td][/tr]
[/table]

E1:E3 are unfilled. At least E1 should be given, right?

What row 4 means is unclear.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Changing date format based on fiscal years versus calendar years
Replies
2
Views
204
Peter_SSs
Peter_SSs
S
  • Question Question
Inventory disposal plan
Replies
3
Views
301
Spaceballs_the_fanboy
S
N
  • Solved
Formula to get the range of certain Category
2 3
Replies
25
Views
806
Peter_SSs
Peter_SSs
Z
  • Question Question
Xlookup with multiple conditions
Replies
5
Views
259
Cubist
Cubist
K
  • Question Question
conditional formatting question
Replies
2
Views
603
kerm007
K

Forum statistics

Threads
1,224,749
Messages
6,180,725
Members
452,995
Latest member
isldboy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top