What's wrong with this formula?

B

Bobo3370

New Member
Joined
Sep 9, 2021
Messages
11
Office Version
  1. 2019
Platform
  1. Windows
=VLOOKUP(A2;IFS(C2="11";Baza_Aktivni_Instrastat_formula!A:H;IFormula!C2="12";Baza_Aktivni_Instrastat_formula!A:H;Formula!D2="FCA";Baza_Aktivni_Instrastat_formula!A:H;Formula!D2="EXW";Baza_Aktivni_Instrastat_formula!A:H;Formula!D2="DAP";Baza_Aktivni_Instrastat_formula!A:H;Formula!D2="CPT";Baza_Aktivni_Instrastat_formula!A:H;Formula!E2="1";Baza_Aktivni_Instrastat_formula!A:H;Formula!E2="2";Baza_Aktivni_Instrastat_formula!A:H;Formula!F2="3";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="BE";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="CZ";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="DE";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="GB";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="HU";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="IT";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="NL";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="PL";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="SE";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="SI";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="BD";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="BE";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="BG";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="CH";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="CN";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="CZ";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="DE";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="ES";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="FR";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="GB";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="HU";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="IE";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="IL";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="IN";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="IT";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="JP";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="KG";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="KR";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="MM";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="MX";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="NL";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="PK";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="PL";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="RO";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="RU";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="SE";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="SI";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="SK";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="TH";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="TN";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="TW";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="US";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="VN";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="XU";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="ZA";Baza_Aktivni_Instrastat_formula!A:H);2;FALSE)
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Hi & welcome to MrExcel.
What is the point of the IFS function? From what I can see you are looking up the same range regardless of anything else.
 
Upvote 0
Hard to tell since it is very long and we don't know what it is supposed to do and we don't have any sample data to test with, but one place to look is with the spelling here

Bobo3370 said:
=VLOOKUP(A2;IFS(C2="11";Baza_Aktivni_Instrastat_formula!A:H;IFormula!C2="12";Baza_Aktivni_Instrastat_formula!A:H;Formula!D2="FCA";Baza_Aktivni_Instrastat_formula!A:H;Formula!D2="EXW";Baza_Aktivni_Instrastat_formula!A:H;Formula!D2="DAP";Baza_Aktivni_Instrastat_formula!A:H;Formula!D2="CPT";Baza_Aktivni_Instrastat_formula!A:H;Formula!E2="1";Baza_Aktivni_Instrastat_formula!A:H;Formula!E2="2";Baza_Aktivni_Instrastat_formula!A:H;Formula!F2="3";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="BE";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="CZ";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="DE";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="GB";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="HU";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="IT";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="NL";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="PL";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="SE";Baza_Aktivni_Instrastat_formula!A:H;Formula!G2="SI";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="BD";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="BE";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="BG";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="CH";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="CN";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="CZ";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="DE";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="ES";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="FR";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="GB";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="HU";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="IE";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="IL";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="IN";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="IT";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="JP";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="KG";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="KR";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="MM";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="MX";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="NL";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="PK";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="PL";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="RO";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="RU";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="SE";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="SI";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="SK";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="TH";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="TN";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="TW";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="US";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="VN";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="XU";Baza_Aktivni_Instrastat_formula!A:H;Formula!H2="ZA";Baza_Aktivni_Instrastat_formula!A:H);2;FALSE)
Click to expand...
 
Upvote 0
Hi guys, sorry but I had to go yesterday. In the mini sheet you will see 2 different excel tables, they are actually in separate worksheets. So in the 1st table, in column B I have to find the description of the material in the 2nd table, but it depends on various conditions.
 
Upvote 0
Knjiga1
ABCDEFGHIJKLMNOPQRSTUV
1MATERIALDESCRIPTIONVPUIVPrZNORIGINMATERIALDESCRIPTIONVPUIVPrZNORIGIN
28431100011CPT13BETW84311000kuka11FCA13DEDE
38431100011EXW23NLNL84311000adapter11FCA13BEUS
48431498011DAP13HUDE84311000prsten11CPT13BETW
58448390011EXW23GBTW84311000prsten11FCA13SESE
68466102011FCA13SESE84311000držač iglica11EXW23NLNL
78466102011EXW23GBGB84311000držač iglica11CPT13DEUS
88467920011CPT13DECN84314980pokretna rola11DAP13HUDE
98467920011FCA13SESE84314980pokretna rola11DAP13DEDE
1084483900pribor11EXW23GBTW
1184661020Držać alata-adapter11FCA13SESE
1284661020Držać alata-trn11EXW23GBGB
1384661020Držać alata-trn11FCA13DEGB
1484679200prigušivač11CPT13BEIE
1584679200odstojnik11FCA13BEZA
1684679200odstojna cijev11CPT13BEFR
1784679200matica11FCA13SECN
1884679200ploča za polirku11FCA13GBGB
1984679200zadnja ploča11CPT13DECN
2084679200čahura11FCA13SESE
2184679200lopatice11CPT13BEXU
2284679200lopatice11CPT13BEZA
2384679200poluga11EXW23DEUS
List1
 
Upvote 0
Bobo3370 said:
but it depends on various conditions.
Click to expand...
It would have been a good idea to explain what those "various conditions" are, rather than helpers having to wade through that enormous formula that apparently does not work anyway. . ;)

Taking a guess ..

21 09 10.xlsm
ABCDEFGHINOPQRSTUV
1MATERIALDESCRIPTIONVPUIVPrZNORIGINMATERIALDESCRIPTIONVPUIVPrZNORIGIN
284311000prsten11CPT13BETW84311000kuka11FCA13DEDE
384311000držač iglica11EXW23NLNL84311000adapter11FCA13BEUS
484314980pokretna rola11DAP13HUDE84311000prsten11CPT13BETW
584483900pribor11EXW23GBTW84311000prsten11FCA13SESE
684661020Držać alata-adapter11FCA13SESE84311000držač iglica11EXW23NLNL
784661020Držać alata-trn11EXW23GBGB84311000držač iglica11CPT13DEUS
884679200zadnja ploča11CPT13DECN84314980pokretna rola11DAP13HUDE
984679200čahura11FCA13SESE84314980pokretna rola11DAP13DEDE
1084483900pribor11EXW23GBTW
1184661020Držać alata-adapter11FCA13SESE
1284661020Držać alata-trn11EXW23GBGB
1384661020Držać alata-trn11FCA13DEGB
1484679200prigušivač11CPT13BEIE
1584679200odstojnik11FCA13BEZA
1684679200odstojna cijev11CPT13BEFR
1784679200matica11FCA13SECN
1884679200ploča za polirku11FCA13GBGB
1984679200zadnja ploča11CPT13DECN
2084679200čahura11FCA13SESE
2184679200lopatice11CPT13BEXU
2284679200lopatice11CPT13BEZA
2384679200poluga11EXW23DEUS
Lookup
Cell Formulas
RangeFormula
B2:B9B2=INDEX(P:P,AGGREGATE(15,6,ROW(P$2:P$23)/((O$2:O$23=A2)*(Q$2:Q$23=C2)*(R$2:R$23=D2)*(S$2:S$23=E2)*(T$2:T$23=F2)*(U$2:U$23=G2)*(V$2:V$23=H2)),1))
 
Upvote 0
Solution
I hope it helps.
Knjiga1
ABCDEFGHIJKLM
1VP1112
2UIFCAEXWDAPCPT
3UI12
4VPr3
5ZNBECZDEGBHUITNLPLSESIPL
6ORIGINBDBEBGCHCNCZDEESFRGBPK
7HUIEILINITJPKGKRMMMXNL
8RORUSESISKTHTNTWUSVNXUZA
List2
 
Upvote 0
Bobo3370 said:
I hope it helps.
Click to expand...
I'm afraid that it doesn't. I don't see what that means.

Why don't you manually fill in the results in column B of the post #5 mini-sheet, post it again and then explain in words exactly how you (manually) get a couple of those results?
 
Upvote 0
I looked cell A2 in the yellow table in column "MATERIAL", after I find the material I have to look if his VP, UI, VPr, ZN and ORIGIN in grey table are the same as in the yellow table, and when I find the conditions that are the same for the Material in cell A2 I write down the "DESCRIPTION" from yellow table.
Problem is that the Material in grey table has numerous variations in yellow table, for example "MATERIAL" 8431100 exists in yellow table in different variations (DESCRIPTION, VP, UI, VPr, ZN and ORIGIN) and a have to get that description from yellow table to grey table. Is that ok?
Knjiga1
ABCDEFGHIJKLMNOPQRSTUV
1MATERIALDESCRIPTIONVPUIVPrZNORIGINMATERIALDESCRIPTIONVPUIVPrZNORIGIN
284311000PRSTEN11CPT13BETW84311000kuka11FCA13DEDE
384311000DRŽAČ IGLICA11EXW23NLNL84311000adapter11FCA13BEUS
484314980POKRETNA ROLA11DAP13HUDE84311000prsten11CPT13BETW
584483900PRIBOR11EXW23GBTW84311000prsten11FCA13SESE
684661020Držać alata-adapter11FCA13SESE84311000držač iglica11EXW23NLNL
784661020Držać alata-trn11EXW23GBGB84311000držač iglica11CPT13DEUS
884679200zadnja ploča11CPT13DECN84314980pokretna rola11DAP13HUDE
984679200čahura11FCA13SESE84314980pokretna rola11DAP13DEDE
1084483900pribor11EXW23GBTW
1184661020Držać alata-adapter11FCA13SESE
1284661020Držać alata-trn11EXW23GBGB
1384661020Držać alata-trn11FCA13DEGB
1484679200prigušivač11CPT13BEIE
1584679200odstojnik11FCA13BEZA
1684679200odstojna cijev11CPT13BEFR
1784679200matica11FCA13SECN
1884679200ploča za polirku11FCA13GBGB
1984679200zadnja ploča11CPT13DECN
2084679200čahura11FCA13SESE
2184679200lopatice11CPT13BEXU
2284679200lopatice11CPT13BEZA
2384679200poluga11EXW23DEUS
List1
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
formula to return two columns of data adjacent to #N/A cell entry
Replies
3
Views
367
Fluff
Fluff
C
  • Question Question
3 payment some payments not made or paid in full
Replies
1
Views
535
CrashOD
C
R
  • Question Question
Is there a quick and easy way to tabulate a list of values in a range?
Replies
0
Views
605
Rhothgar
R
G
  • Question Question
IF AND nesting and how to avoid the 64 nest limit
Replies
3
Views
683
Fluff
Fluff
G
  • Question Question
Question on Hlookup
Replies
1
Views
552
Eric W
Eric W

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top