Vlookup

Jlamarre

New member
Joined
Nov 29, 2018
Messages
22
Reaction score
0
Points
0
Excel Version(s)
2016
Hello, this might be related to a problem in this thread.

I read somewhere that a vlookup might be slower then a index/match formula. But for the life of me I can't get it working. (even my vlookup on the information I read looking for does not work). No idea why.

Now my file is sheet1 (a table) that will gather information from other sheets in the same folder. The other sheets are getting theire information from an Sql query. So in each of the other sheets I have a copy of the sql table. (is this the best setup?)

Should I use index/match instead of vlookup?

My file hangs often. That's what I'm trying to resolve.

Thanks
Jl


Sent from my SM-G955W using Tapatalk
 
Last edited by a moderator:
External links being potentially unsafe and not available to all our members, please post your sheet on the forum ( no pics please - Thx)
 
Here is a spreadsheet which teaches you how to do index/match.

There is an error on the spreadsheet It should say Below are the steps to get to the formula in F2 above, not I2.
 

Attachments

  • index match.xlsx
    12.2 KB · Views: 23
Last edited:
Why not simply correct the spreadsheet before uploading it? :confused2:
 
I found the error after I uploaded it.
 
So have you replaced the uploaded spreadsheet with the corrected version? If not, please do so. Thanks.
 
Here is a spreadheet that shows you how to write an index match formula.
 

Attachments

  • indexmatch2.xlsx
    13 KB · Views: 23
Here is a spreadheet that shows you how to write an index match formula.
Thank you guys.

I did some tests with the file provided. It works! I even did some changes to the file as my file has everything in pivot tables. And it worked. Even changed the table from one sheet to another. Still worked. I was eager to try it out on my file.

I don't know if it's because I'm in French but no joy. It's not working.

My problem in the formula is that it can't find my reference in the array. I'm trying to replicate the [#all] after the tables name (in French [#tous]) but with no luck.

Here is what I tried and it worked (on my cell phone with the Excel that is with my phone). Then tried it on my terminal server (Office 365) with no luck.

INDEX(Table5[#All],MATCH(Table4[@Salesperson],Table5[[#All],[salesperson]],0),3)

I'll try again tomorrow. At this time my server just kicked me out for backups. And it's also a good idea to hit the sack. It's past 2300h. 6 more hours to start the last day of the week.

Thank you again. I'll keep you posted.

JL

Sent from my SM-G955W using Tapatalk
 
Hello all sorry for not responding sooner. Had a crazy day.

Tried it at work and had no success. I had to stop and work. I will try later.

Thanks for your time.

Sent from my SM-G955W using Tapatalk
 
Hi,

I found this thread from my Analysis Tool of my Excel-Translator Website. And I would like to take the opportunity to
thank AliGW for linking to my tool here and on other forums too. Thanks!

I have a note: the online formula translator can not translate table specifiers yet. This is something planned for the next
version - I hope, I will be ready this summer. While Excel functions have been translated to many languages, but not
to all available 91 languages, the table specifiers are! For example, #All is #Tout in French, #Alle in German, #Tutti
in Italian, #모두 in Korean, #すべて in Japanese, #Konke in Zulu or #ყველა in Georgian and many more. So, this
needs some more complex changes to my tool.

Best,
Mourad
 
Last edited:
Here is a copy of the file. It is cleaned up as almost everything in has been removed.

One line in tab Feuil1. This is where I place the items I need to order.
That tab refers to the other tabs for information.

Tab "List" is from an old excel list of our parts. I add the supplier that I need to buy from and the qty per box.

Tab "Tbl Achat Entete" comes from a connection to the SQL database. The table is to get the date the purchase was made. (that's what I'm currently trying to do)

tab "Tbl Produits_Acomba" is the list of our items in stock. Again with a connection to the SQL database. For product name, min/max, price etc.

tab "Tbl Achat_Ligne_Stats_Acomba" is to get the information on did we receive everything.

Ok now I tried to get the date orderd in the "Feuil1" "Date d'achat" column.

The information is in Tab "Tbl Achat Entete" column 9 (or "InDate").

I tried with a Vlookup and a index/match with no success.

Can you guys help me out please?

And can someone tell me if we can have this type of file without having to have all the tab with the database in them or an other Excel file with that information (tables).

Thank you.
 

Attachments

  • Copie de A COMMANDER.xlsx
    50.8 KB · Views: 20
Jlamarre,

Your formula is correct. You can prove this by going to cell A7 of Tbl Achat Entete and typing in 63998. After typing this in you should notice that 63998 moves to the right of the cell. Cell J1 on Feuil1 will now show a date.

I have a solution to change the invoice numbers on the
Tbl Achat Entete worksheet. Put a 1 in cell a16 of the Feuil1 worksheet. Copy the 1 by typing Control C. Highlight the invoice numbers in column 1. Go to paste special multiply.

So by multiplying all of the invoice numbers by 1 we change them to a form that Excel can understand.

I am not familiar with SQL. I would suggest that you talk to the SQL person at your company and ask him/her if the numbers can be loaded into Excel in format that Excel understands.

I can think of other solutions also. If you are interested let me know.

In reply to your last question you could have all of the SQL files in a different workbook, if you prefer.
 
Last edited:
Thanks Heyjoe,

You gave me the hint that I needed. In the table the column that the cell is referring to was in a text format. I changed it to numbers and it woks.

JL

Jlamarre,

Your formula is correct. You can prove this by going to cell A7 of Tbl Achat Entete and typing in 63998. After typing this in you should notice that 63998 moves to the right of the cell. Cell J1 on Feuil1 will now show a date.

I have a solution to change the invoice numbers on the
Tbl Achat Entete worksheet. Put a 1 in cell a16 of the Feuil1 worksheet. Copy the 1 by typing Control C. Highlight the invoice numbers in column 1. Go to paste special multiply.

So by multiplying all of the invoice numbers by 1 we change them to a form that Excel can understand.

I am not familiar with SQL. I would suggest that you talk to the SQL person at your company and ask him/her if the numbers can be loaded into Excel in format that Excel understands.

I can think of other solutions also. If you are interested let me know.

In reply to your last question you could have all of the SQL files in a different workbook, if you prefer.
 
Back
Top