How to use VLOOKUP with mixed "look up_values" in multiples files and worksheets?

[rgmatos3]

Dear All,

Once again I am here to ask for help. This time, it is a little more complex.

I need to import data, to a single file (number 01), from two differents daily files (they are always the same and come by email every day and could be salved .xls or .xlsx, I called them (02) and (03)); and other two files (they are filled out by hand, every day, one with data in three worksheets (04) and the other with only one worksheet (05), they are saved as .xlsx). I can work with all files open, it is not a problem.
=IFERROR(VLOOKUP(... did not work. I do not have a clue about VBA. I believe the solution involves ISERROR, IFERROR and VLOOKUP, but how? You will realize that the greatest difficulty is the mixed lookup values, cods and dates.

Please, help!

Best wishes,

Ricardo.

[bluesoftsbl2]

Hi!
i understand that you must import data from other places, other excel file.
This is my opinion, i share file 1 in network(LAN,Internet), and all people will import data in file 1, file 1 will update data automatic.
I can and if you agrees with me. I will try to help you by teamview. OK

[rgmatos3]

Dear Bluesoftsbl2,

You see, that is possible, but in real life file 02 have 345 values per day; file 03 have 49 values per day; and the other two files have one value per day per worksheet. Besides that, values in file 01 are complete mixed up.
It must be use IFERRO and VLOOKUP. But how?

Thanks anyway!

Ricardo.

[Speshul]

I don't quite follow the problem but the usage of IFERROR vs ISERROR is:

With IFERROR you can make a statement, if it's an error do something else

With ISERROR, you can make a statement and it will return either a TRUE or a FALSE. Because of this, it must be nested within an IF to get result of an IFERROR (this is what needed to be used before IFERROR existed)

Synax:

IFERROR(VLOOKUP(A1,B1:B30,2,FALSE),"An Error Happened")


IF(ISERROR(VLOOKUP(A1,B1:B30,2,FALSE)),"An Error Happened","An Error Didn't happen")
Or, to make it work like IFERROR
IF(ISERROR(VLOOKUP(A1,B1:B30,2,FALSE)),"An Error Happened",VLOOKUP(A1,B1:B30,2,FALSE))

Hope this is in some way useful.

[rgmatos3]

Dear Speshul,

I am very glad for your consideration, but the problem lies in the different types of search ranges: codes in columns and dates in lines.
You must see the exemple, it is hard even to describe.

Thanks,

Ricardo.

[p24leclerc]

Sorry to say this, but it is really hard to even try to help you on this as I don't even see a formula in your file.
And the way your other files are structured, it will be really hard to come up with a formula.
Can you change the file's format so the dates repeat in each row?

[bluesoftsbl2]

Dear Bluesoftsbl2,

You see, that is possible, but in real life file 02 have 345 values per day; file 03 have 49 values per day; and the other two files have one value per day per worksheet. Besides that, values in file 01 are complete mixed up.
It must be use IFERRO and VLOOKUP. But how?


Thanks anyway!

Ricardo.

I know. In my ways, you will do 5 sheet in a workbook, user 1 import in sheet 1, user 2 import in file 2...you will share and mange workbook in my server computer. In this workbook, you will do all fomula then share, so the other people will only import data.
In addition, you can authorize for the other people, example user 1 only do ịn sheet 1, user 2 only read workbook or do in sheets 2...
So, you will not use IFERRO and vlookup. I thinks that. and i agree p24leclerc.

[rgmatos3]

Pierre,

Thank you very much! I will show the case:

Your solution worked just fine for the first two files:

=IFERROR(VLOOKUP($A4;'C:\Users\RICARDO2\Desktop\PROCV\VLOOKUP\[02_Daily_File_06_01.xlsx]ITG JJJ'!$E$4:$F$13;2;FALSE);(VLOOKUP($A4;'C:\Users\RICARDO2\Desktop\PROCV\VLOOKUP\[03_Another_Daily_File_06_01.xlsx]YJJ!$E$4:$F$6;2;FALSE)))

01_All_Together.xlsx


Than, every day, I drag the first cell to the right and replace date 06_day for date 06_day+1. OK.

The problem: is it possible to use more than one IFERROR? Or something else that could handle values in daily rows? Is it possible to mix up rows and columns?

Files 01, 02 and 03 are unchangeables, sorry.

It is more a question, if it is not possible, someone can fill it up by hand, every day.

Thanks again!

Ricardo.

[rgmatos3]

Dear Bluesoftsbl2,

IFERROR with VLOOKUP works fine for the first two files. I have tried something like =IFERROR(IFERROR(IFERROR(VLOOKUP(...));(VLOOKUP(...));(VLOOKUP(...));(VLOOKUP(...));(VLOOKUP(...));(VLOOKUP(...)))))))).
But it did not work at all.

Thanks again!

Ricardo.

[p24leclerc]

If you can work with all the files open, try this workbook.
You just have to copy the formula across your columns and the formula will look at the right data for this date.
I'm still looking at a formula that can work even of the workbooks are closed but I'n not sure it works.
Regards

[bluesoftsbl2]

If you can work with all the files open, try this workbook.
You just have to copy the formula across your columns and the formula will look at the right data for this date.
I'm still looking at a formula that can work even of the workbooks are closed but I'n not sure it works.
Regards

ok my skype is sonktpm2. i will do and up video. I think it is ok. wating for me.

[p24leclerc]

After some search, it is impossible to get information from a closed workbook with a formula using the function INDIRECT.

[boopathiraja]

Since you are arriving the values from vlookups, and your codes are exclusive for respective sheets, and you are looking to manipulate horizontal and vertical criteries,

You Could Use Sumifs instead of vlookups, you can build your formula criteria either horizontal/vertical easily when you use sumifs

Sumifs(files1)+sumifs(file2)+etc...

Try it out and let us know

Dear Bluesoftsbl2,

IFERROR with VLOOKUP works fine for the first two files. I have tried something like =IFERROR(IFERROR(IFERROR(VLOOKUP(...));(VLOOKUP(...));(VLOOKUP(...));(VLOOKUP(...));(VLOOKUP(...));(VLOOKUP(...)))))))).
But it did not work at all.

Thanks again!

Ricardo.

[boopathiraja]

Have tried with for first two sheets and getting the expected results, (before drag the date value down in column A of sheet02 and sheet03

Please Login or Register  to view this content.

Since you are arriving the values from vlookups, and your codes are exclusive for respective sheets, and you are looking to manipulate horizontal and vertical criteries,

You Could Use Sumifs instead of vlookups, you can build your formula criteria either horizontal/vertical easily when you use sumifs

Sumifs(files1)+sumifs(file2)+etc...

Try it out and let us know

[boopathiraja]

Have tried with for first two sheets and getting the expected results, (before drag the date value down in column A of sheet02 and sheet03

=SUMIFS('[02_Daily_File_06_01-1.xlsx]ITG JJJ'!$F:$F,'[02_Daily_File_06_01-1.xlsx]ITG JJJ'!$E:$E,$A4,'[02_Daily_File_06_01-1.xlsx]ITG JJJ'!$A:$A,B$3)+SUMIFS('[03_Another_Daily_File_06_01-1.xlsx]YJJ'!$F:$F,'[03_Another_Daily_File_06_01-1.xlsx]YJJ'!$E:$E,$A4,'[03_Another_Daily_File_06_01-1.xlsx]YJJ'!$A:$A,B$3)

[bluesoftsbl2]

If you can work with all the files open, try this workbook.
You just have to copy the formula across your columns and the formula will look at the right data for this date.
I'm still looking at a formula that can work even of the workbooks are closed but I'n not sure it works.
Regards

I have sent you my youtube link. You can check and see it, pls.

[rgmatos3]

Dear Pierre,
I am terribly sorry. I got really sick the past two weeks, a very strong flu. I saw your INDIRECT solution. Thank you very much, but now I realize that, besides closed files, the values are note completed at once. You see, some of it come by night, some in the dawn, some we had to ask for it by email or phone. I would end up with three or four "-", and I believe that will bring some trouble, because "All together" file it is not the last, but a source which will be used to export data to another file (someone was paid to do it), and finally (I can't image how) will be imported to a corporate website (another fellow was hired).
It is not my work! I am a chemist!
I'm really grateful, but it seems me someone will have to complete one by one, by hand every day. Even my boss are a little confused.
I'm staying with your past solution. It is enough.
Thank you very much.
Ricardo.

[rgmatos3]

Dear boopathiraja,

I am very sorry, but you see, SUMIFS do not work properly with closed files. I will prefer IFERROR since it works fine in closed or open files.
Thanks again.

Ricardo.

[rgmatos3]

bluesoftsbl2!!!!
You are a very helpful chap! God bless you! But I follow orders, my boss asked me for a formula, and I have to come up with one. In fact, Pierre did, last march.
Thanks again. See your notifications board.
Thanks again!

[rgmatos3]

bluesoftsbl2!!!!
You are a very helpful chap! God bless you! But I follow orders, my boss asked me for a formula, and I have to come up with one. In fact, Pierre did, last march.
Thanks again!

[bluesoftsbl2]

bluesoftsbl2!!!!
You are a very helpful chap! God bless you! But I follow orders, my boss asked me for a formula, and I have to come up with one. In fact, Pierre did, last march.
Thanks again!

Thanks very much! God bless you!
I hope it will help you and your boss. You can share it to your boss and all other people. It is very good knowledge and so I want all people to know about add-in a-tools.
And I'm happy to help you now and in the future by add-in a-tools if you want.
Thanks.