Using LOOKUP function for if-then statement

[Gucci Mane]

After viewing the following guide (http://office.microsoft.com/en-us/ex...005209118.aspx), I'm still having troubles creating if-then statements for a model I've been trying to construct. I need to test more than seven conditions, and need to use cells from different sheets as well - the LOOKUP function looks like it would get the job done. However, I keep running into errors. This is the current format I've been trying:

=LOOKUP(A1,{1,2,3},{'Sheet1'!A1,'Sheet1'!A2,'Sheet1'!A3})

Where A1 is the cell accepting the conditional value, {1,2,3} are the conditional values, and {'Sheet1'!A1,'Sheet1'!A2,'Sheet1'!A3} are three cells from a different sheet that should be returned (these correspond with the three values).

What am I doing wrong? I've been searching for a good amount of time on this site and the Internet, and have found nothing useful. I don't know any VBA but if this could be solved using it then I'd be willing to give it a try.

[NBVC]

If your lookup values are as you show, 1, 2, 3, etc.... Try instead CHOOSE... LOOKUP doesn't allow you to use cell references in the { } array

=CHOOSE(A1,'Sheet1'!A1,'Sheet1'!A2,'Sheet1'!A3)

or if indeed your range is Sheet1A1:A3 for 1,2,3, lookup values...

=INDEX('Sheet1'!A1:A3,A1)

if not, a more representative example of lookup values and lookup ranges should be given as example.

[Gucci Mane]

I should have specified, the return values I'm using aren't a range but seperate, discrete values. Here is the exact example I'm using:

=LOOKUP('Hypothetical Summary (2)'!F12,{7,8,9,10,11,12,13},{'Hypothetical Summary (2)'!B62,'Hypothetical Summary (2)'!B71,'Hypothetical Summary (2)'!B80,'Hypothetical Summary (2)'!B89,'Hypothetical Summary (2)'!B98,'Hypothetical Summary (2)'!B107,'Hypothetical Summary (2)'!B116})

Where {7,...,13} are the input values and "Hypothetical Summary (2)" is the sheet where I'm sourcing all the corresponding values.

I'm not quite sure I understand how to use the CHOOSE function in context of what I'm trying to do, could you explain further? Thanks for the help!

[NBVC]

The CHOOSE should work:

=6+CHOOSE('Hypothetical Summary (2)'!F12,'Hypothetical Summary (2)'!B62,'Hypothetical Summary (2)'!B71,'Hypothetical Summary (2)'!B80,'Hypothetical Summary (2)'!B89,'Hypothetical Summary (2)'!B98,'Hypothetical Summary (2)'!B107,'Hypothetical Summary (2)'!B116)

[Gucci Mane]

Thanks for the quick response. I tried the function you posted, but it only worked with one input (7) and returned #VALUE! for all other inputs. Also, the "6+" in the beginning of the function just added the quantity 6 to the number that was supposed to be returned - I don't think it did what it was supposed to.

I think I might be a little unclear in explaining what I want to do: the input 7 in cell F12 from sheet "Hypothetical Summary (2)" should return the value of B62 from the same sheet, input 8 from that sheet should return the value of B71, etc. This is all being done on a different sheet ("Report Summary").

[NBVC]

Should've been:

=CHOOSE('Hypothetical Summary (2)'!F12-6,'Hypothetical Summary (2)'!B62,'Hypothetical Summary (2)'!B71,'Hypothetical Summary (2)'!B80,'Hypothetical Summary (2)'!B89,'Hypothetical Summary (2)'!B98,'Hypothetical Summary (2)'!B107,'Hypothetical Summary (2)'!B116)

Edit... don't know where my head it at... changed again.

[Gucci Mane]

That worked! Much appreciated. Just out of curiosity, could you briefly explain how this formula works, in particular, the inclusion of "-6" after the input cell? I think I will need to accommodate for more data - a total of 19 input values {1,...,19} and an equal number of corresponding cells, and I'm wondering how I can adapt the current configuration of the formula you posted to account for this. Thanks again

[NBVC]

=CHOOSE(index_num,value1,value2,....)

Choose uses an index_num, starting from 1 to get the corresponding value from the list of values following the index_num.

So if your index_num is 1 it gets Value1, if it 2 it gets Value2.. and so on.

Since your values are consecutively from 7:13, then if you subtract 6, you start at 1, and are able to get the corresponding value based on starting always with 1.

so as long as the input values are consecutive or have a pattern that you can achieve a consecutive range starting from 1, then you can use CHOOSE()