Hi
I was attempting to match data from table 1 with data from table 2 and return the corresponding value. I have been using the following formula to no avail. Can you please assist.
=IF(ISERROR(VLOOKUP(BG2,$BR:$CB,1,FALSE)),0,VLOOKUP(BG2,$BR:$CB,1,FALSE))
I have attached the excel file.
What is supposed to be in BG2?
what would your answer look like?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thank you so much for a prompt reply. Below is what I was trying to achieve.
Table 3 - would show following
Jun-16 Jul-16 Aug-16
A22=T22 B22=U22 B22=V22
A23=T23 B23=U23 B23=V23
A24=T24 B24=U24 B24=V24
A25=T25 B25=U25 B25=V25
Can you show that in your sample file please
I have uploaded modified attachment. Please check.
That appears teh be the same file as the 1st 1
Sorry the attached is the updated file.
Sorry, Im not following what you want.
In W3, where is the 23 coming from?
what does A2=T2 etc mean?
Wlak me though what you are doing please
Sorry about all the confusion. I am trying to show that IF data in column A matches data in column T (matching the header month in column T) than show the values from column T
=IF(A3=$T$2,T2,"")
I think the OP is trying to get data for eight months ahead.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I am trying to create a spreadsheet for reorder of stock. As in some cases there is a lead time of 4 months I am trying to say that if stock is required in Oct 2016 with a lead time of 4 months we need to order in June 2016.
OK, so will the lead time change, or will it be constant?
The lead time changes but I have already apply the lead time in Table 1. All I need to do now is match the data from Table 1 with data from Table 2 and have a final order by month in Table 3
Yes, but I don't think the way you are doing it is necessarily the most efficient way. I think you can probably make it much easier by having a cell on your sheet where you type the number of months' lead time as a number (in the case of the example, 8), and then reference this cell in your lookup formula. All you are really doing is looking for an offset value. I would get rid of the section of the table where you have stated the month, as it's not necessary.
Does the attached help at all? The formula in M3 can be drag copied across and down.
Oh thank you so much that is a great idea I have attached the actual sheet would you be able to assist with formula as suggested.
You need to have enough data in the sheet to cope with the lead time. For example, if you are calculating the number that you need to order in April 2017 for eight months hence, then your main table needs to have columns that go that far ahead.
I would set this out differently: I would put the forecast table on a separate sheet.
However, try this in Q2, drag copied across and down:
=HLOOKUP(OFFSET(Q$1,,$C2),$E$1:$O$29,ROW(),FALSE)
If you want to get rid of the error message:
=IFERROR(HLOOKUP(OFFSET(Q$1,,$C2),$E$1:$O$29,ROW(),FALSE),"")
Last edited by AliGW; 05-22-2016 at 02:38 AM.
What the worksheet is meant to show is that eg. due to a 4 months lead time to receive a June 2016 replenishment, I need to place an order in Feb 2016 and the quantity to be ordered in Feb 2016 will match current sales Qty listed in June 2016.
Sorry - I think this is what the sheet does!
Please tell me what your expected results in Q2 to AA2 would be.
Oh you are an absolute Champion! that worked perfectly, thank you so much for your assistance with this! I appreciate your time very much.
You are very welcome!
And apologies to Ford for jumping in ...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks