Hi all
I have a little problem with Excel, not being a pro in this field, I'm calling on you! I will try to explain my problem to you as clearly as possible:
1 - I have an "American Screw Standard" sheet, this is what I call the "interface", this sheet has the purpose, by selecting via a drop-down menu, my diameter (ISO) and my screw length (ISO) that I would like to switch to the American "equivalent".
2 - A "Resources" sheet with all the data in "bulk" in tables.
Problem: I can't associate my two boxes concerning the "American" standard so that it automatically comes up with the right designation! I tried with functions such as "Vlookup" I can't do it...
I'll send you the excel file if I wasn't clear enough.
Thank you in advance if you have a solution!
zz_classeur_norme_porte_outil_vis.xlsx
Hello;
If you want to use the VLOOKUP functions:
Removes the two lookups from the "Equivalent" cells (Standard Sheet... "
Transform your data table and "Structured Table", it will be easier to manage.
In your search cell "American Diameter Equivalent" writes: =VLOOKUP(B3; Table 1; 3; FALSE)
with B3 = The diameter of the screw (Value to be searched)
Table1 = the New Name of your data table (resource sheet)
3= the number of columns separating the value to be displayed from the value you are looking for. (With the VLOOKUP function: the value to be searched for is necessarily in the first column of the search box.
The FALSE at the end of the formula determines whether Excel should look for the exact value or a close value.
Then in the cell "American Length Equivalent": the formula will be
=VLOOKUP(C3; Table 1[[ISO Screw Length]:[US Screw Length]]; 3; FALSE)
I attach my proposal in Excel format.
Note: depending on your version of excel it is possible to use VLOOKUP instead of VLOOKUP (this function is limited by the position of the values to be searched)
Kind regards
copie_de_zz_classeur_norme_porte_outil_visb.xlsx
3 Likes
Hello
Thank you, I had understood how the function worked after spending a good amount of time on it^^ Now it's even clearer, I should have organized myself a little better and I would have succeeded on my own I think!
Have a good day and thank you again!
1 Like