Searching a data table

SFCChase

SFCChase

Board Regular
Joined
Jun 25, 2013
Messages
107
Office Version
  1. 2016
Platform
  1. Windows
Is there a way to search a data table using multiple criteria?

Example
[TABLE="width: 244"]
<COLGROUP><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 48pt" span=4 width=64><TBODY>[TR]
[TD="class: xl72, width: 69, bgcolor: transparent"] [/TD]
[TD="class: xl72, width: 64, bgcolor: transparent"]22-26[/TD]
[TD="class: xl72, width: 64, bgcolor: transparent"]27-31[/TD]
[TD="class: xl72, width: 64, bgcolor: transparent"]32-36[/TD]
[TD="class: xl72, width: 64, bgcolor: transparent"]37-41[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]# Pushups[/TD]
[TD="class: xl72, bgcolor: transparent"]PTS[/TD]
[TD="class: xl72, bgcolor: transparent"]PTS[/TD]
[TD="class: xl72, bgcolor: transparent"]PTS[/TD]
[TD="class: xl72, bgcolor: transparent"]PTS[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]45[/TD]
[TD="class: xl72, bgcolor: transparent"]66[/TD]
[TD="class: xl72, bgcolor: transparent"]66[/TD]
[TD="class: xl73, bgcolor: transparent"]69[/TD]
[TD="class: xl74, bgcolor: transparent"]71[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]46[/TD]
[TD="class: xl72, bgcolor: transparent"]67[/TD]
[TD="class: xl72, bgcolor: transparent"]67[/TD]
[TD="class: xl73, bgcolor: transparent"]70[/TD]
[TD="class: xl74, bgcolor: transparent"]72[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]47[/TD]
[TD="class: xl72, bgcolor: transparent"]68[/TD]
[TD="class: xl72, bgcolor: transparent"]68[/TD]
[TD="class: xl73, bgcolor: transparent"]71[/TD]
[TD="class: xl74, bgcolor: transparent"]73[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]48[/TD]
[TD="class: xl72, bgcolor: transparent"]69[/TD]
[TD="class: xl72, bgcolor: transparent"]69[/TD]
[TD="class: xl73, bgcolor: transparent"]72[/TD]
[TD="class: xl74, bgcolor: transparent"]74[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]49[/TD]
[TD="class: xl72, bgcolor: transparent"]70[/TD]
[TD="class: xl72, bgcolor: transparent"]71[/TD]
[TD="class: xl73, bgcolor: transparent"]73[/TD]
[TD="class: xl74, bgcolor: transparent"]75[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]50[/TD]
[TD="class: xl72, bgcolor: transparent"]71[/TD]
[TD="class: xl72, bgcolor: transparent"]72[/TD]
[TD="class: xl73, bgcolor: transparent"]74[/TD]
[TD="class: xl74, bgcolor: transparent"]76[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]51[/TD]
[TD="class: xl72, bgcolor: transparent"]73[/TD]
[TD="class: xl72, bgcolor: transparent"]73[/TD]
[TD="class: xl73, bgcolor: transparent"]75[/TD]
[TD="class: xl74, bgcolor: transparent"]77[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]52[/TD]
[TD="class: xl72, bgcolor: transparent"]74[/TD]
[TD="class: xl72, bgcolor: transparent"]74[/TD]
[TD="class: xl73, bgcolor: transparent"]76[/TD]
[TD="class: xl74, bgcolor: transparent"]78[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]53[/TD]
[TD="class: xl72, bgcolor: transparent"]75[/TD]
[TD="class: xl72, bgcolor: transparent"]75[/TD]
[TD="class: xl73, bgcolor: transparent"]77[/TD]
[TD="class: xl74, bgcolor: transparent"]79[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]54[/TD]
[TD="class: xl72, bgcolor: transparent"]76[/TD]
[TD="class: xl72, bgcolor: transparent"]76[/TD]
[TD="class: xl73, bgcolor: transparent"]78[/TD]
[TD="class: xl74, bgcolor: transparent"]81[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]55[/TD]
[TD="class: xl72, bgcolor: transparent"]77[/TD]
[TD="class: xl72, bgcolor: transparent"]77[/TD]
[TD="class: xl73, bgcolor: transparent"]79[/TD]
[TD="class: xl74, bgcolor: transparent"]82[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]60[/TD]
[TD="class: xl72, bgcolor: transparent"]83[/TD]
[TD="class: xl72, bgcolor: transparent"]82[/TD]
[TD="class: xl72, bgcolor: transparent"]85[/TD]
[TD="class: xl72, bgcolor: transparent"]87[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]65[/TD]
[TD="class: xl72, bgcolor: transparent"]89[/TD]
[TD="class: xl72, bgcolor: transparent"]90[/TD]
[TD="class: xl72, bgcolor: transparent"]90[/TD]
[TD="class: xl72, bgcolor: transparent"]92[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]70[/TD]
[TD="class: xl72, bgcolor: transparent"]94[/TD]
[TD="class: xl72, bgcolor: transparent"]95[/TD]
[TD="class: xl72, bgcolor: transparent"]95[/TD]
[TD="class: xl72, bgcolor: transparent"]97[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]75[/TD]
[TD="class: xl72, bgcolor: transparent"]100[/TD]
[TD="class: xl72, bgcolor: transparent"]100[/TD]
[TD="class: xl72, bgcolor: transparent"]100[/TD]
[TD="class: xl72, bgcolor: transparent"]100[/TD]
[/TR]
</TBODY>[/TABLE]


I'd like to be able to search an age (say 27) and see how many pts they get for doing 53 pushups (75 in this case). I'd like that 75 to fill in cell C1 when I put 25 in A1 and 53 in B1.

Any help out there?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
If you can change your header so that the Min Age is in Row 1 rather than the ranges you can use this formula:
=INDEX(E4:H17,MATCH(B1,D3:D17,1),MATCH(A1,E1:H1,1))


[TABLE="class: grid, width: 588"]
<colgroup><col span="4"><col><col span="4"></colgroup><tbody>[TR]
[TD] [/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]76[/TD]
[TD] [/TD]
[TD]22[/TD]
[TD]27[/TD]
[TD]32[/TD]
[TD]37[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]to 26[/TD]
[TD]to 31[/TD]
[TD]to 36[/TD]
[TD]to 41[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]# Pushups[/TD]
[TD]PTS[/TD]
[TD]PTS[/TD]
[TD]PTS[/TD]
[TD]PTS[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]72[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]73[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]74[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]76[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]77[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]78[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]79[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]81[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]79[/TD]
[TD="align: right"]82[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]82[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]87[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]92[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]97[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]59[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
jduley said:
Sorry, formula in C1 should be =INDEX(E4:H17,MATCH(B1,D4:D17,1),MATCH(A1,E1:H1,1))
Click to expand...


Thanks for the help....I had to do a little tweaking to work with my actual data (table is really A1:AL228) but it worked out!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
No letters again 0 or - or *
Replies
4
Views
321
PeteWright
PeteWright
M
  • Solved
Chose random % of row from sheet1 and paste in to sheet2
Replies
3
Views
234
motilulla
M
welshgasman
  • Solved
Have chart show each row of data?
Replies
2
Views
58
welshgasman
welshgasman
togenmoser
  • Solved
Finding all possible combinations of 2 sums
2
Replies
10
Views
689
togenmoser
togenmoser
welshgasman
  • Question Question
Formulae jumping references?
Replies
7
Views
76
welshgasman
welshgasman

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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