I need someone really good with formulas, lookup or index/match

A

anzer

Board Regular
Joined
Mar 5, 2015
Messages
67
Hello,

I have a report for work to do:

[TABLE="width: 500"]
<tbody>[TR]
[TD]2/02[/TD]
[TD]part#[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB11[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="width: 80"]Part[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="width: 80"]Entry date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="width: 130"]Stock at entry date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]stock[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB11[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2/02[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2/04[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/03[/TD]
[TD]part#[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2/05[/TD]
[TD]?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]stock[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2/07[/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/04[/TD]
[TD]part#[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB17[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]stock[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/05[/TD]
[TD]part#[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB11[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]stock[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]3[/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/06[/TD]
[TD]part#[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB11[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB15[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]stock[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]3[/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/07[/TD]
[TD]part#[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="class: xl65, width: 80"]AB14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]stock[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]3[/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need in "Stock at entry date" a way to get the stock then an order was enterred. For exemple, AB11:3, AB14:3, AB20:0 and AB18:6. AB14should give 3 instead of 5 because i need the highest number in stock only, not the SUM of the parts. I've tried Index with Watch but it's not working ...or i just don't know how to use it properly. The stock tables has names on it (if it can help, it's like: 0202stock for the stock at 02/02). Also what may be a problem is that parts number are not necesseraly in alphabetical order, they all mixed up and some days we just have 4 part in stock when we also have 6 part in stock next day. So just to be clear, Stock at entry date should give the stock i had at the entry date of the part number, and it should give the highest stock, not the SUM. Please help me with this!!

Thank you very much
 
Last edited:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
I'm going to assume since your data seems to expand to the right there could be many columns added, so therefore the results table is not to the right but either below the dataset or a different worksheet.
I'm also going to assume that Stock row is always 2 rows below the respective part and date information.

=MAX(IF((INDIRECT(MATCH(B33,datecolumn,0)&":"&MATCH(B33,datecolumn,0)))=A33,INDIRECT(MATCH(B33,datecolumn,0)+2&":"&MATCH(B33,datecolumn,0)+2),""))
Click to expand...

B33 - Date to Look Up
A33 - Part to Look Up
datecolumn - Range of dates that seem to be in your column A.

CTRL+SHIFT+ENTER
 
Upvote 0
Hello,

Yes my data do expand to the right and yes results table is in another work sheet.
Stock row is 1 row below part and date (i just need to change +2 to +1)

I enterred the formulas but it work only for AB20 (0) and not the 3 others :S

Do you have any clue??

Thank you for your help!!
 
Upvote 0
I fixed it except the first one when i do cntrl shift enter i have a message saying: Excel can't caulate this formula because of an open file with reference and that is it impossible to show mistaken references. try to modify last formulas or delete it ...
 
Upvote 0
Correct, just change +2 to +1.

Check your datecolumn range and make sure it's locked with $column$row for all.

Don't forget CTRL+SHIFT+ENTER

If it still doesn't work I'll have to know exactly where your data is located.
 
Last edited:
Upvote 0
It's working when i'm working on the same sheet but as soon as the results are in another sheet, it gives 0 to every results
 
Upvote 0
On a different worksheet this is what you'll need to do:

$A$1:$A$27 - data column reference range, only change the END $27 only, no matter where your data starts in the column
Sheet3 - your worksheet name
B16 - date to lookup
A16 - item to lookup

In case your worksheet name consists of 2 words you may need to add ' for MATCH sheet reference. Like MATCH(B16,'Sheet Name'!$A$1:$A$27,0)


=MAX(IF((INDIRECT("'Sheet3'!"&MATCH(B16,Sheet3!$A$1:$A$27,0)&":"&MATCH(B16,Sheet3!$A$1:$A$27,0)))=A16,INDIRECT("'Sheet3'!"&MATCH(B16,Sheet3!$A$1:$A$27,0)+1&":"&MATCH(B16,Sheet3!$A$1:$A$27,0)+1),""))
Click to expand...
 
Last edited:
Upvote 0
Hello Chicagocomputer class! since you gave me the formulas everything has been better my me excel project!
But now i have occured another problem!
i'd like you to help me!

I have on a sheet :
Item number (lets say G10 ) and date (E10)

On another sheet i have my stock report ( as seen on the first post of this page)

Date : item 1 item 1 item 2 ....
25ft 30ft 50 ft ...

date ....item....
....

So what i want is on sheet 1 (with item G10) and date (E10)
when i write in G10 the part number i want a every lenght on the same part number written, like a list of all the lenghts at that date

IS it easily doable?

Thank you
 
Last edited:
Upvote 0
Hello Chicagocomputer class! since you gave me the formulas everything has been better my me excel project!
But now i have occured another problem!
i'd like you to help me!

I have on a sheet :
Item number (lets say G10 ) and date (E10)

On another sheet i have my stock report ( as seen on the first post of this page)

Date : item 1 item 1 item 2 ....
25ft 30ft 50 ft ...

date ....item....
....

So what i want is on sheet 1 (with item G10) and date (E10)
when i write in G10 the part number i want a every lenght on the same part number written, like a list of all the lenghts at that date

IS it easily doable?

Thank you
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
Vlookup
Replies
4
Views
378
Peter_SSs
Peter_SSs
Dazzawm
  • Solved
Macro To Find Latest Date To Corresponding Data In Column B And Add To Sheet 2
Replies
7
Views
341
Fuji
F
K
  • Solved
Index with Multiple Conditions
Replies
2
Views
65
kivikatz
K
C
  • Solved
index match excel formula to vba code
Replies
9
Views
332
DanteAmor
DanteAmor
W
  • Question Question
Copy formula across 28 day dynamic shift calendar
Replies
4
Views
334
Wolfgang17
W

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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