Lookup help

N

nickjbrowne

New Member
Joined
Jan 24, 2006
Messages
10
I need help making the correct formula to find a value in an array that matches a column and row search criteria.

For example, in the table below if I want to find the quantity sold for Part number AAA0003 on 1/2/2010 my formula would return 147.

No clue how to do this.

Can anyone help?


Date
Part number 1/1/2010 1/2/2010 1/3/2010 1/4/2010 1/5/2010
AAA0002 101 109 139 144 143
AAA0003 143 147 144 108 134
AAA0004 125 105 129 142 131
AAA0005 141 105 120 136 114
AAA0006 143 129 126 110 120
AAA0007 132 123 125 139 132
AAA0008 141 136 108 118 100
AAA0009 116 110 142 101 110
AAA0010 106 137 124 102 143
 
Sort by date Sort by votes
If your data are in A1:F10:

A12: 01/02/2010
A13:AAA003
A14:=INDEX($B$2:$F$10,MATCH(A13,$A$2:$A$10,0),MATCH(A12,$B$1:$F$1,0))
 
Last edited:
Upvote 0
Thank you for the quick replay. One question though, what does the A12 and A13 refer to? The part number or the date?
 
Upvote 0
nickjbrowne,


Excel Workbook
ABCDEFGHIJ
1Part number1/1/20101/2/20101/3/20101/4/20101/5/2010Part numberDateQty Sold
2AAA0002101109139144143AAA00031/2/2010147
3AAA0003143147144108134
4AAA0004125105129142131
5AAA0005141105120136114
6AAA0006143129126110120
7AAA0007132123125139132
8AAA0008141136108118100
9AAA0009116110142101110
10AAA0010106137124102143
11
Sheet1
 
Upvote 0
nickjbrowne,


Working with two worksheets:


Excel Workbook
ABCDEF
1Part number1/1/20101/2/20101/3/20101/4/20101/5/2010
2AAA0002101109139144143
3AAA0003143147144108134
4AAA0004125105129142131
5AAA0005141105120136114
6AAA0006143129126110120
7AAA0007132123125139132
8AAA0008141136108118100
9AAA0009116110142101110
10AAA0010106137124102143
11
Date





Excel Workbook
ABC
1Part numberDateQty Sold
2AAA00031/2/2010147
3
Results
 
Upvote 0
An alternative fomula that gives the same results.

Excel Workbook
ABCDEF
1Part number01/01/201001/02/201001/03/201001/04/201001/05/2010
2AAA0002101109139144143
3AAA0003143147144108134
4AAA0004125105129142131
5AAA0005141105120136114
6AAA0006143129126110120
7AAA0007132123125139132
8AAA0008141136108118100
9AAA0009116110142101110
10AAA0010106137124102143
11
12
13Part NumberDateQty Sold
14AAA000301/02/2010147
Sheet1
 
Upvote 0
Excellent! That worked! That will save a ton of time each week.

And I didn't know you could do that with a vlookup formula.

Thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Help on putting a number into a range
2 3
Replies
22
Views
433
Peter_SSs
Peter_SSs
C
  • Question Question
Excel Formula to keep records of drawn cards
Replies
3
Views
432
Cquake
C
A
  • Question Question
VBA to Categorise based on columns
Replies
3
Views
194
Peter_SSs
Peter_SSs
M
  • Question Question
VBA code failing and miscalculating when running high numbers of lines (200+) but ok when doing it line by line
Replies
3
Views
162
DanteAmor
DanteAmor
T
  • Question Question
cross country meet scoring
Replies
7
Views
1K
Joe4
Joe4

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