Excel VLOOKUP help

[che6mw]

Long shot but hopefully someone here can help.

I am using the VLOOKUP function in excel.

now I can happily get it working with the format =VLOOKUP(AA11, AA7:AB10, 1), for instance.

where AA7:AB10 is obviously an array. But what I'd really love to do is be able to change the array used from another cell. So for instance I have two or three named arrays - say Array1, Array2, Array3.

And the array to be used is selected from a drop down box. The VLOOKUP function will then use that array to return the answer.

I know how to do this using VBA. Also I can't use another IF statement as the cell with the VLOOKUP formula has the maximum number already.

Any ideas? Am I missing something?

[che6mw]

always the way ... ask someone else for help and then it suddenly comes to you yourself!

If anyone else has had a similar problem I suggest you look at the INDIRECT function.

Very useful.

[Ace-T]

You can link several sets of if statements across several cells if that is any help?

Can't quite see what you are trying to do so if you want to PM me with a sample of the sheet I would be happy to take a look. Not promising anything though

Edited to add: I was too late

Ace-T

Edited by Ace-T on Monday 22 January 21:27

[che6mw]

Ace-T said:

You can link several sets of if statements across several cells if that is any help?

Sadly can't use IF statements as forumla in a cell is already huge. it looks like this:

=(IF(YEAR($Q6)>YEAR($Q$6), IF((VLOOKUP((DATE(R$5,MONTH($Q6),DAY($Q6))),INDIRECT($AS$6),(MATCH(DATE(R$5,MONTH(R$4),1),INDIRECT($AS$7),)+2),))="",#N/A,VLOOKUP((DATE(R$5,MONTH($Q6),DAY($Q6))),INDIRECT($AS$6),(MATCH(DATE(R$5,MONTH(R$4),1),INDIRECT($AS$7),)+2))), (IF(MONTH(R$4)>4, (IF(VLOOKUP((DATE(R$5,MONTH($Q6),DAY($Q6))),INDIRECT($AS$6),(MATCH(DATE(R$5,MONTH(R$4),1),INDIRECT($AS$7),)+2),)="", #N/A, VLOOKUP((DATE(R$5,MONTH($Q6),DAY($Q6))),INDIRECT($AS$6),(MATCH(DATE(R$5,MONTH(R$4),1),INDIRECT($AS$7),)+2),))), (IF(VLOOKUP((DATE(R$5-1,MONTH($Q6),DAY($Q6))),INDIRECT($AS$6),(MATCH(DATE(R$5,MONTH(R$4),1),INDIRECT($AS$7),)+2),)="", #N/A, VLOOKUP((DATE(R$5-1,MONTH($Q6),DAY($Q6))),INDIRECT($AS$6),(MATCH(DATE(R$5,MONTH(R$4),1),INDIRECT($AS$7),)+2),)))))))




Thanks for the help anyway!

[Ace-T]

che6mw said:

Ace-T said:

You can link several sets of if statements across several cells if that is any help?

Sadly can't use IF statements as forumla in a cell is already huge. it looks like this:
=
Thanks for the help anyway!

You can make your if statements larger than the (7 I think) nested ifs that Excel allows by bracketing 6 with a 7th that says if this lot doesn't work, go run the formulas in this next cell and so on...

If you want to pm me I will send you a sample of how I did it.

Cheers

Ace-T

[che6mw]

Ace-T said:

You can make your if statements larger than the (7 I think) nested ifs that Excel allows by bracketing 6 with a 7th that says if this lot doesn't work, go run the formulas in this next cell and so on...

If you want to pm me I will send you a sample of how I did it.
Cheers
Ace-T

So it is still limited to 7 in a given cell? To be honest if it starts needing multiple cells (i.e. more than 7 if statements spread across more than one cell) then I find the spreadsheet rapidly becomes messy and so I usually move to VBA at that point where I'm not aware of a limit to the number of IF statements.