Help! Referencing a cell based on ranged criteria.

M

missdeannamarie

New Member
Joined
Mar 31, 2014
Messages
25
Hello!

I have been stuck on this formula for quite some time and was hoping I could get some help from you genius excel people! Below is an example of my spreadsheet. What I am trying to achieve is a formula in Cell R2, that matches the value of cell B23 with cell range C1:Q1 (B23 has a drop down that chooses C1:Q1 values, so I would need R2 to change based on what is selected) and provides the value for the row R2 is in.

I hope that makes sense! I apologize ahead of time if this is super easy or has been on another thread, i have checked as much as i can.

Any help is much appreciated! Thank you!!!


[TABLE="width: 886"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Products[/TD]
[TD][/TD]
[TD]OCT-15[/TD]
[TD]NOV-15[/TD]
[TD]DEC-15[/TD]
[TD]JAN-16[/TD]
[TD]FEB-16[/TD]
[TD]MAR-16[/TD]
[TD]APR-16[/TD]
[TD]MAY-16[/TD]
[TD]JUN-16[/TD]
[TD]JUL-16[/TD]
[TD]AUG-16[/TD]
[TD]SEP-16[/TD]
[TD]OCT-16[/TD]
[TD]NOV-16[/TD]
[TD]DEC-16[/TD]
[TD]RESULT[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]SCM1[/TD]
[TD][/TD]
[TD]25[/TD]
[TD]174[/TD]
[TD]165[/TD]
[TD]148[/TD]
[TD]95[/TD]
[TD]25[/TD]
[TD]42[/TD]
[TD]69[/TD]
[TD]156[/TD]
[TD]192[/TD]
[TD]129[/TD]
[TD]200[/TD]
[TD]20[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]SCM2[/TD]
[TD][/TD]
[TD]99[/TD]
[TD]93[/TD]
[TD]89[/TD]
[TD]45[/TD]
[TD]48[/TD]
[TD]58[/TD]
[TD]89[/TD]
[TD]126[/TD]
[TD]18[/TD]
[TD]85[/TD]
[TD]10[/TD]
[TD]200[/TD]
[TD]20[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]SCM3[/TD]
[TD][/TD]
[TD]62[/TD]
[TD]87[/TD]
[TD]94[/TD]
[TD]96[/TD]
[TD]94[/TD]
[TD]44[/TD]
[TD]93[/TD]
[TD]55[/TD]
[TD]63[/TD]
[TD]49[/TD]
[TD]154[/TD]
[TD]200[/TD]
[TD]20[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]SCM4[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]200[/TD]
[TD]133[/TD]
[TD]85[/TD]
[TD]166[/TD]
[TD]10[/TD]
[TD]142[/TD]
[TD]15[/TD]
[TD]76[/TD]
[TD]69[/TD]
[TD]72[/TD]
[TD]200[/TD]
[TD]20[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]SCM5[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]200[/TD]
[TD]133[/TD]
[TD]85[/TD]
[TD]166[/TD]
[TD]10[/TD]
[TD]142[/TD]
[TD]15[/TD]
[TD]76[/TD]
[TD]69[/TD]
[TD]72[/TD]
[TD]200[/TD]
[TD]20[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]SCM6[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]200[/TD]
[TD]133[/TD]
[TD]85[/TD]
[TD]166[/TD]
[TD]10[/TD]
[TD]142[/TD]
[TD]15[/TD]
[TD]76[/TD]
[TD]69[/TD]
[TD]72[/TD]
[TD]200[/TD]
[TD]20[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]QA1[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]200[/TD]
[TD]133[/TD]
[TD]85[/TD]
[TD]166[/TD]
[TD]10[/TD]
[TD]142[/TD]
[TD]15[/TD]
[TD]76[/TD]
[TD]69[/TD]
[TD]72[/TD]
[TD]200[/TD]
[TD]20[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]QA2[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]200[/TD]
[TD]133[/TD]
[TD]85[/TD]
[TD]166[/TD]
[TD]10[/TD]
[TD]142[/TD]
[TD]15[/TD]
[TD]76[/TD]
[TD]69[/TD]
[TD]72[/TD]
[TD]200[/TD]
[TD]20[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]QA3[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]500[/TD]
[TD]133[/TD]
[TD]85[/TD]
[TD]166[/TD]
[TD]10[/TD]
[TD]142[/TD]
[TD]15[/TD]
[TD]76[/TD]
[TD]69[/TD]
[TD]72[/TD]
[TD]200[/TD]
[TD]20[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]QA4[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]200[/TD]
[TD]133[/TD]
[TD]100[/TD]
[TD]166[/TD]
[TD]10[/TD]
[TD]142[/TD]
[TD]15[/TD]
[TD]76[/TD]
[TD]69[/TD]
[TD]72[/TD]
[TD]200[/TD]
[TD]20[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]QA5[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]200[/TD]
[TD]133[/TD]
[TD]85[/TD]
[TD]166[/TD]
[TD]10[/TD]
[TD]142[/TD]
[TD]15[/TD]
[TD]76[/TD]
[TD]69[/TD]
[TD]72[/TD]
[TD]200[/TD]
[TD]20[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]QA6[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]200[/TD]
[TD]133[/TD]
[TD]85[/TD]
[TD]166[/TD]
[TD]10[/TD]
[TD]142[/TD]
[TD]15[/TD]
[TD]76[/TD]
[TD]69[/TD]
[TD]72[/TD]
[TD]200[/TD]
[TD]20[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]FAC1[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]200[/TD]
[TD]133[/TD]
[TD]85[/TD]
[TD]166[/TD]
[TD]10[/TD]
[TD]142[/TD]
[TD]15[/TD]
[TD]76[/TD]
[TD]69[/TD]
[TD]72[/TD]
[TD]200[/TD]
[TD]20[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]FAC2[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]200[/TD]
[TD]133[/TD]
[TD]85[/TD]
[TD]166[/TD]
[TD]10[/TD]
[TD]142[/TD]
[TD]15[/TD]
[TD]76[/TD]
[TD]69[/TD]
[TD]72[/TD]
[TD]200[/TD]
[TD]20[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]FAC3[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]200[/TD]
[TD]133[/TD]
[TD]85[/TD]
[TD]166[/TD]
[TD]10[/TD]
[TD]142[/TD]
[TD]15[/TD]
[TD]76[/TD]
[TD]69[/TD]
[TD]72[/TD]
[TD]200[/TD]
[TD]20[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]FAC4[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]200[/TD]
[TD]133[/TD]
[TD]85[/TD]
[TD]166[/TD]
[TD]10[/TD]
[TD]142[/TD]
[TD]15[/TD]
[TD]76[/TD]
[TD]69[/TD]
[TD]72[/TD]
[TD]200[/TD]
[TD]20[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]FAC5[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]200[/TD]
[TD]133[/TD]
[TD]85[/TD]
[TD]166[/TD]
[TD]10[/TD]
[TD]142[/TD]
[TD]15[/TD]
[TD]76[/TD]
[TD]69[/TD]
[TD]72[/TD]
[TD]200[/TD]
[TD]20[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]FAC6[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]200[/TD]
[TD]133[/TD]
[TD]85[/TD]
[TD]166[/TD]
[TD]10[/TD]
[TD]142[/TD]
[TD]15[/TD]
[TD]76[/TD]
[TD]69[/TD]
[TD]72[/TD]
[TD]200[/TD]
[TD]20[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]OP1[/TD]
[TD][/TD]
[TD]50[/TD]
[TD]200[/TD]
[TD]133[/TD]
[TD]85[/TD]
[TD]166[/TD]
[TD]10[/TD]
[TD]142[/TD]
[TD]15[/TD]
[TD]76[/TD]
[TD]69[/TD]
[TD]72[/TD]
[TD]200[/TD]
[TD]20[/TD]
[TD]200[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Total Sales[/TD]
[TD][/TD]
[TD]986[/TD]
[TD]3854[/TD]
[TD]2476[/TD]
[TD]1664[/TD]
[TD]2893[/TD]
[TD]287[/TD]
[TD]2496[/TD]
[TD]490[/TD]
[TD]1453[/TD]
[TD]1430[/TD]
[TD]1445[/TD]
[TD]3800[/TD]
[TD]380[/TD]
[TD]3800[/TD]
[TD]3800[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]Select[/TD]
[TD]OCT-15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes

Excel 2010
ABCDEFGHIJKLMNOPQR
1Products15-Oct15-Nov15-Dec16-Jan16-Feb16-Mar16-Apr16-May16-Jun16-Jul16-Aug16-Sep16-Oct16-Nov16-DecRESULT
2SCM125174165148952542691561921292002020020025
3SCM2999389454858891261885102002020020099
4SCM3628794969444935563491542002020020062
5SCM4502001338516610142157669722002020020050
6SCM5502001338516610142157669722002020020050
7SCM6502001338516610142157669722002020020050
8QA1502001338516610142157669722002020020050
9QA2502001338516610142157669722002020020050
10QA3505001338516610142157669722002020020050
11QA45020013310016610142157669722002020020050
12QA5502001338516610142157669722002020020050
13QA6502001338516610142157669722002020020050
14FAC1502001338516610142157669722002020020050
15FAC2502001338516610142157669722002020020050
16FAC3502001338516610142157669722002020020050
17FAC4502001338516610142157669722002020020050
18FAC5502001338516610142157669722002020020050
19FAC6502001338516610142157669722002020020050
20OP1502001338516610142157669722002020020050
21Total Sales98638542476166428932872496490145314301445380038038003800986
22
23Select15-Oct
Sheet7
Cell Formulas
RangeFormula
R2=HLOOKUP($B$23,$C$1:$Q$21,ROW(A2),0)
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
396
rahildhody
R
M
  • Question Question
Displaying number based on date conditions column and True/False column.
Replies
2
Views
247
mike4777
M
S
  • Question Question
Inventory disposal plan
Replies
3
Views
277
Spaceballs_the_fanboy
S
C
  • Question Question
Excel Formula to keep records of drawn cards
Replies
3
Views
269
Cquake
C
GMC The Macro Man
  • Solved
Using =SUM(SMALL to count but omitting Zero
2
Replies
11
Views
219
GMC The Macro Man
GMC The Macro Man

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

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