If and Vlookup formula; too many arguments

[Zheno]

Hey.

Im not sure if all the formulas that i meantion in this thread will be 100 correct since im not using a english version of excel.

Im trying to build a little database and the closest thing i have come to manage what i want to do is with IF and Vlookup function.

I have 1 "main page" lets call it "sheet1"
Then i have nomerous of "secondary pages" we call them "sheet2", "sheet3" etc.

The idea is that on my "main page" im using 3 colums "A","B" and "C". "A" and "B" helps deciding where my VLOOKUP function should find the correct value.

The "A" column is planned to decide in what sheet to look for seach word(which is written in "C" column)

Basicly if "A1" is saying "2" its gonna do my VLOOKUP in "sheet2" , and if "A1" sais "3" its gonna look for my "search word" in "sheet3" etc.

My formula at this point (witch is working for 2 pages)
=IF(A1=2;VLOOKUP(B1;sheet1!A1:B6;2;FALSE);IF(A1=3;VLOOKUP(B1;sheet2!A1:B6;2;FALSE)))

This is working perfectly.
If i write "2" in "A1" and "car" in "B1" VLOOKUP jumps to "sheet1" lookup "car" and return the value in the second column (in this case 2"
and if i write "3" in "A1" and keep "car" in "B1" VLOOKUP jumps to "sheet2" and return the carvalue for this sheet (in this case 22).


Then the problem
The problem is ofcourse that if i wanna continue with this formula in the same box, i wanna make it keep looking in more sheets depending on what number i have in "A1"
If i put number 5 in it goes to "sheet5" and look for "car" and return valuve.

But at this point the formula is too big for excel.

So i guess my question is. Is there any workaround for this? Can anyone come up how to approach this in another way? (im out of ideas)
Or am i doomed and have to learn programming to get my idea to work?


Any questions let me now. I can upload a excel example with my idea if its wanted.

Any answears appriciated.

/Magnus

[DonkeyOte]

How many sheets are you talking about ?

You could use CHOOSE to determine the range

Please Login or Register  to view this content.

You could use INDIRECT which would be shorter in terms of formula length but it would be Volatile and would require both a consistent sheet naming convention and consistent range

Please Login or Register  to view this content.

It all boils down to ultimate requirements...

Note in your post your narrative and sample formula do not tie out - you say if A1 is 2 use Sheet2 yet your formula uses Sheet1, you also say word to find is in C yet your refer to B - I've assumed the sample formula to be incorrect.

[Zheno]

Yeah some translations from my excel to the post mixed up alittle.

Im gonna try these ones you mentioned out (thanks for them). And letting know how it worked out.

It probelry have to handle up to 10 sheets.

If these methods dosent work im gonna upload a file to easier see how im hoping everything to work out.

/Magnus

[Zheno]

How many sheets are you talking about ?

You could use CHOOSE to determine the range

Please Login or Register  to view this content.

Great!

The CHOOSE function sorted my probplem with "too many arguments"

A new problem tho, im not sure if im just slow at the moment
This sheet is planned to work for pricing piping prices (im in the metal industri)
I have 13 sheets which are called
Main(where the typing is gonna be) and my databases is called as follow
2",3",4",6",8",10",12",14",16",18",20" and 24"

In my main page i have 3 cathegories. A1(Dimension) B1(type) C1(returned value)

In C2 i have my VLOOKUP formula looking like this

Please Login or Register  to view this content.

In B2 is my lookup word.

And in A1 (i guess its the index for what page to go to)
But i want to type in my dimension here if that is possible.

As it is now when i put "2" in A2(the dimension) and "pipe" in B2(type) it returns the value for the 3" sheet, i guess since its the second page in my VLOOKUP/CHOOSE formula

again if i put "6" in my dimension field and "pipe" in type. logicly this is then returning the valuve in my 10" sheet, since its the sixth sheet in my formula.

Is there any way to go around this, or do i have to create "ghostpages" inbetween as in empty pages to sort the dimension->right sheet out?

Or is it just that i have been sitting with this too long and cant see the obvious?

[JBeaucaire]

CHOOSE will require there be a value in each "slot" for the array of possible answers. If you have no "3" answer, then you'll need to include the field, but leave it empty.

[Zheno]

CHOOSE will require there be a value in each "slot" for the array of possible answers. If you have no "3" answer, then you'll need to include the field, but leave it empty.

Yeah i thought so, was hoping for something else
Thanks anyway


Maybe this isnt the forum but i ask anyway.
Could there be a way to use some kind of database, Access? SQL?

Like depending what i have been writing in a excel field it look it up in Access?

For example:

If i write my A1 field in excel and ID number, call it 1234, it connects to access, looks for the ID 1234 in a tableformat, and returns all the values that is connected qith the ID 1234


If i make a table in Access/SQL looking like this

ID dimen price
1 20 111
2 21 22
1234 100 2

Then i have my excelsheet. and in my A1 cell i write the ID number 1234.
And it would in turn look in my database for the ID 1234 and return the values for dimension and price in my B1 and C1 cell.

If this is possible i guess its time to learn Access.

As i said, i know this forums isnt about access but it dosent hurt to ask

[DonkeyOte]

CHOOSE will require there be a value in each "slot" for the array of possible answers. If you have no "3" answer, then you'll need to include the field, but leave it empty.

The other option is of course to use a MATCH call to determine index_num in the CHOOSE, ie along the lines of:

Please Login or Register  to view this content.

in the above you therefore have only "x" number of outputs in the CHOOSE where "x" is determined by the number of sheets in the MATCH inline array.

Could there be a way to use some kind of database, Access? SQL?

Certainly but that's a bigger project (esp. if you've not used either Access or SQLServer before) - and as you say this is an Excel Forum so we could help regards retrieving the info but not regards creating your db - they would be questions best suited to an Access / SQLServer forum
(there is an Access forum here).

What I would say from an XL standpoint is that you could perhaps think of utilising a Pivot Table.
If you have one sheet in your file that stores the data as you outline in your prior post (re: DB table) then you can feed that data into a PT and do lots of analysis very quickly and very neatly with little or no Excel formula knowledge required.

If you want further assistance on the DB / PT matters please setup a new thread (in appropriate forum) - as a rule we try to keep threads dedicated to the "original" question (ie the VLOOKUP / CHOOSE)

[Zheno]

Thank you guys for all the answears. Im gonna take a look into Pivot table and see what i can do with that.

Gonna mark this as solved since i dont think i will get further with using vlookup/choose formats.