Vlookup, 9 different variables, 9 different table array.

[oddcarout]

I need to be able to do a look up in 9 different table arrays using 9 different ranges.

I have attached a worksheet to help explain. My problem is that I can't have 9 IF statements in one formula.

I am not sure how to make this smaller or work.

Thanks

[DonkeyOte]

You could use named ranges with INDIRECT but that would be volatile, another way would perhaps be:

Please Login or Register  to view this content.

[NBVC]

Try:

=VLOOKUP(B26,OFFSET($A$3:$R$8,,MATCH(A26,$A$2:$R$2,0)-2),2,0)

copied down.

[yurttas]

Try this:

=IF(A26=1;VLOOKUP(B26;$A$2:$B$8;2;0);0)+IF(A26=2;VLOOKUP(B26;$C$2:$D$8;2;0);0)+IF(A26=3;VLOOKUP(B26;$E$2:$F$8;2;0);0)+IF(A26=4;VLOOKUP(B26;$G$2:$H$8;2;0);0)+IF(A26=5;VLOOKUP(B26;$I$2:$J$8;2;0);0)+IF(A26=6;VLOOKUP(B26;$K$2:$L$8;2;0);0)+IF(A26=7;VLOOKUP(B26;$M$2:$N$8;2;0);0)+IF(A26=8;VLOOKUP(B26;$O$2:$P$8;2;0);0)+IF(A26=9;VLOOKUP(B26;$Q$2:$R$8;2;0);0)

You should replace ; with ,


.

[DonkeyOte]

yurttas, if you opt for the separate VLOOKUPs you could shorten that by using CHOOSE to select the range based on value of A, ie:

Please Login or Register  to view this content.

[yurttas]

yurttas, if you opt for the separate VLOOKUPs you could shorten that by using CHOOSE to select the range based on value of A, ie:

Please Login or Register  to view this content.

That is good idea...

.

[NBVC]

But it also assumes that the sample data is similar to the actual data... i.e. the Ranges are named, consecutively 1, 2, 3, 4, 5, 6, 7, 8, 9

[yurttas]

But it also assumes that the sample data is similar to the actual data... i.e. the Ranges are named, consecutively 1, 2, 3, 4, 5, 6, 7, 8, 9

I hope so.

[DonkeyOte]

Well, it doesn't rely on named ranges but yes I agree it assumes that the ranges can be established based on a consecutive numbering system of values in A, ie A values aren't 1,34,100 etc...
Incidentally I wasn't advocating it necessarily, for flexibility I'd still probably opt for the INDEX/MATCH approach in post#2, more calcs than #3 but non-volatile.... that said I'm pretty sure there's a more elegant method that avoids INDIRECT/OFFSET I/we just haven't thought of it as yet...

[NBVC]

Didn't mean named ranges, per se.. I meant the numbers in the cells that refer to the Ranges are those consecutive numbers...

[NBVC]

I also don't know that "volatile" is always bad.. most of the time the timing, calculating, etc is not that noticeable. More calcs can slow the machine down too...

[DonkeyOte]

Re: Volatiles - it's most definitely a personal preference thing...

I always avoid wherever possible simply because I don't know with any great certainty as to how a model will develop over time (or how it is being used by others) and thus what was once not a significant overhead could potentially become so ... I concede that Volatiles are frequently the most convenient option - OFFSET when INDEX:INDEX becomes too complex for it's own good and like everyone else I would use INDIRECT if for whatever reason INDEX was not viable (ie I had to have a variable worksheet reference in my function).

My 2c

[oddcarout]

I have moved this into my real worksheet.

=VLOOKUP(P17,OFFSET('input page'!B34:S44,,MATCH(L17,'input page'!B32:S32,0)-2),2,0)

the input page looks like the sandbox version. I am getting an #N/A.

P17 is the same as sandboxB26
L17 is the same as sandboxA26

am I missing something?

[NBVC]

Without seeing the "real" workbook or reasonable facsimile (i.e. confidential stuff out)... then hard to say...

Are you making the ranges absolute, so that when you copy down, the ranges don't change?

Are you including the first row under the titles that may have text..

=VLOOKUP(P17,OFFSET('input page'!$B$33:$S$43,,MATCH(L17,'input page'!$B$32:$S$32,0)-2),2,0)

Are the formats of column B in and row 32 the same as the formats in P17 and L17?

Can you attach a sample that more closely resembles the actual?

[oddcarout]

here you go I have taken all the confidential info out. looks pretty plain.

[NBVC]

Well it looks like your columnar ranges are not exact.. i.e. you have bottom of ranges.. so when you are looking up 2183 (which in P17), it won't find it, since you don't have that exact number... to find closest match, you need to eliminate the last 0 in the Vlookup function.

=VLOOKUP(P17,OFFSET('input page'!$B$33:$S$43,,MATCH(L17,'input page'!$B$32:$S$32,0)-2),2)

Also note, there is no actual data in your Title 5 area of the input sheet... which would give an #N/A error too...

[oddcarout]

thank you,

it seemes so simple when you point it out.