Vlookup

[Jlamarre]

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

[Pecoflyer]

External links being potentially unsafe and not available to all our members, please post your sheet on the forum ( no pics please - Thx)

[Heyjoe]

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.

[AliGW]

Why not simply correct the spreadsheet before uploading it?

[Heyjoe]

I found the error after I uploaded it.

[AliGW]

So have you replaced the uploaded spreadsheet with the corrected version? If not, please do so. Thanks.

[Heyjoe]

Here is a spreadheet that shows you how to write an index match formula.

[Jlamarre]

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

[AliGW]

I don't think you need to change #All for a French locale. Try this:

=INDEX(Table5[#All];EQUIV(Table4[@Salesperson];Table5[[#All];[salesperson]];0);3)

(courtesy of https://en.excel-translator.de/translator/)

[AliGW]

Heyjoe - thanks for uploading the corrected workbook!