Data Validation List, IF Statements, and Iteration

[thefreeline]

Hello,

I have a data validation list in Sheet1:A2 that will change a set of data in C2:F10 when a selection is made.

In Sheet2, I would like to create a formula that says something like this (I know how it would work in SQL, just not Excel):

WHERE S1:A2 = 'x' CHOOSE C2 (value associated with 'x')
WHERE S1:A2 = 'y' CHOOSE C2 (value associated with 'y')

An IF statement here doesn't seem to work because it only returns a value IF a certain value is selected in S1:A2. I need to choose the corresponding cell value AS-IF I certain list item were chosen.

I'll try to attach a picture of what I have in mind...

http://www.flickr.com/photos/thefreeline/6391014271/

 

[Jerry Sullivan]

Hello and Welcome to the Board,

The link you posted doesn't seem to be working, but your description was pretty clear.

If I'm understanding you correctly, a common way to handle this is through the use of one of the lookup functions.

If your DV list source is Sheet1!$AA$2:$AA$10, place your associated values in Sheet1!$AB$2:$AB$10, then in Sheet2 use the formula:

=VLOOKUP(Sheet1!A2,Sheet1!$AA$2:$AB$10,2,FALSE)