Formula with date

Dear All

Encl pls find the table which contains two worksheet.
One worksheet base file name Base file
Other one name BAF file.

The goal is to get the data from BAF file which accordingly to the date period to fit into the Base file whenever I type the date on start date (from), then it will show the BAF value automatically.

I would like to set up simple formula which does not need to use VBA & also pls use as simple formula as possible.

Did anyone can assist ?

Tks in advance.

Beginner !

Originally Posted by FDibbins

Try this...
=IFERROR(VLOOKUP(E2,'BAF file'!$E$2:$G$5,3,0),"")

Tks for your reply but it does not work for the date on row 2 since the date is not on 1/4 (is from 16/4).
Actually, the data in BAF file can be from 1/4 to 30/6 & I want to get the value in Base file which I type in the date from & to, then they can find those data in between those date.

Originally Posted by Sambo kid

in your file the information in columns A, B, C and D are the same in both tabs. Will this always be the case or will what you want in the Base File tab have to match on more information than the "From" and "To" date fields?

In the real file which are many column in between those file, which means that it will have many other information.
In A,B,C,D,E,F which are the information which affect the BAF value.
IN BAF table which has date validity, it can be either 1/4 to 2/4 or 1/4 to 30/6.
I would like need to key in the base file From & TO, then it need to find the result from BAF table base on those range.

Hope it's clear ?!

Originally Posted by alansidman

If I understand correctly, then

=INDEX('BAF file'!$G$2:$G$5,MATCH('Base File'!E2,'BAF file'!$E$2:$E$5,1))

in G1 and copy down.

Dear Alan

Great with tks. I think it's exactly the answer that I am try to look for the date solution.
However, would you help to made the formula which can combine with the data on column A,B,C,D too ?
I can combine all those column : A2&B2&C2&D2 into new column : H so that it need to match with those information too.

Tks in advance !

Dear Dave,

Tks. The result is the same but if I add the row with Jun rate on Base file which it does not change the value for Jun so I think the formula is better on Post 4 - Alan as it's change automatically.

But anyway, appreciate your help !

Originally Posted by alansidman

I am not understanding your issue. Please clarify with a step by step narration of what you want to accomplish as this is new information that was not spelled out in the original post.

Dear Alan

I att'd update file for your reference.
In fact, beside those date, I need the data(value) match with ABCD column so I create the column H.
(I think this is the solution to create the formula link between BAF file & Base file to have the same information.)
If you think that it does not need it which will be more great !

Tks in advance !
Ale

Originally Posted by FlameRetired

There are extra spaces in the concatenated text in both sheets column H, and they are inconsistent.

FEMED CMADry will not match FEMED CMA Dry.

I cleaned those up and this works.

In the attached please see

Formula:

Please Login or Register  to view this content.

Dear FlameRetired

Tks for your formula, i tried in my real file & it's work !!!! I had made some amendment due to the worksheet can't show N/A message which easy to solve with IFERROR.

Thank you so much !

Best Regards
Ale

Originally Posted by FlameRetired

You are welcome. Glad to help.

Dear FlameRetired

I have problem in formula again, pls find att'd file which has error message.
Can you assist to solve it ?

Basically it's because there are two period of date from BAF table & I will put the date range from Base file. It need to extract the data correctly in the Base file;

Tks again in advance
Ale

If I understand correctly, then

=INDEX('BAF file'!$G$2:$G$5,MATCH('Base File'!E2,'BAF file'!$E$2:$E$5,1))

in G1 and copy down.

ok I'm sorry

Dear Dave

Tks for your reply but it seems that the formula does not work.

I att'd the file that after I input your forumla on it & the data is not the same as your print screen.
Row 6 has error message with #NUM !" on it & other mistakes on the file.

Fyi, I am using version 2016 which I don't know if it's the reason that the result is not the same.

At the same time, when I type the formula which the sign (,) whcih need to type ( in order to pass the formula. It might be the version Windows is in French ?? As when I pass it to my colleague & I noticed that his version 2010 is using (,) too.
But he send me back the file which the formula change back to ( automatically.

=INDEX('BAF file'!$G$2:$G$9;SMALL(IF((H2='BAF file'!$H$2:$H$9)*((E2>='BAF file'!$E$2:$E$9)*(E2<='BAF file'!$F$2:$F$9)+(F2>='BAF file'!$E$2:$E$9)*(F2<='BAF file'!$F$2:$F$9));ROW($H$2:$H$9)-MIN(ROW($H$2:$H$9))+1);COUNTIFS(E$2:E2;E2;F$2:F2;F2;H$2:H2;H2)))

When you use H2 which does not need to refer to BASE file but when I do the formula, it appears the long file name.
Will it be any problem that I just use H2 like yours ?

One last question, as the BAF file which actually is not only 9 rows.
Can i use the column number to put into the formula so that it can cover all the data in that column?

Tks your for your patience & really tks for your help in advance !

Originally Posted by FlameRetired

I am having difficulty understanding.

Please upload a file with all desired results hand typed in. Perhaps I can figure out that way.

Dear Dave,

Encl pls find those file & my explaination on it.
Hope it's clear !

Tks & Rgds
Ale

Originally Posted by FlameRetired

Thank you. That helped.

Please check cell G9. I get 300 which I believe is correct.

The enclosed will take care of the regional settings "," vs ";".

The formula must be array entered --- Ctrl + Shift + Enter

Formula:

Please Login or Register  to view this content.

Dear Dave

Tks for your renew formula but it's still have few problems as below:-


1. I noticed that the row/line number in the formula need to match, example 9 rows in BAF table & BASE file table. However, in my real file, the rows number in BAF table which is 173 but my Base file row number which is nearly 18000.
So I am not sure if it's this problem to cause some error.
Also, when I add the line/row in BAF table, it seems that it need to include the new row/line number to extract the information. Is there any solution to avoid it ? (eg BAF table add one line for 10 & 11 which the result are not the same in Base file)

2. When the data is not yet input it in BAF table (eg rate on July), it seems that they take the first record from the file which it's happen in my real file. Can it show error message N/A so that I can input the data.

3. When the date on Base file which is 1/4 to 30/6 but the BAF file record has data from 1/4 to 30/4 but it show error message. I suppose it should show the Apr data on it.

I encl the file & show in Orange colour & in RED on the cell itself with some explanation.

Tks again for your effort !
Rgds
Ale

Dear Dave,

Yesterday, i retried your formula & actually even the real file has 18000 line which they had no problem.

I am just thinking the principal how to implement this formula & clear my idea. Might be you can still find the solution on it.

Basically, in Base file, I just need to clarify the start date (no need to concern the end date) which is between the date from BAF table (from / to) or not.
If it's in that range, then search for data which match with helper information.
if not, then it's N/A
The formula should allow add the new row information on both Base file & BAF file which will not affect the data.

The problem should be if the data on BAF table has two months information (eg Mar & Apr) which the formula need to find which data should be in Base file accordingly.

Anyway, Tks so much for your help & I hope that one day I can find the solution on it !

B. Rgds
Ale