Can Lookup or Index be used to reference to different cells?

[mrgillus]

Can they be used to reference data based on data inserted into more than one cell? I have attached an example as its kind of confusing to explain.

[NBVC]

Try:

=INDEX($J$2:$L$8,1,MATCH(A1,Color,0))

[mrgillus]

Thanks for the prompt reply. I apologize as I may not have been too clear on the result I was trying to get. Basically I want to try and reference to either table depending on which drop down is used. However, I want them both to go to the same cell. In other words it would be like there are two different index formulas for A3... I'm kind of thinking this would not be possible as it will create conflicting data..

[NBVC]

So is based on priority? For example, if A1 has a selection, then use that, or if A1 is blank and A2 has a selection go with that?

I.E. How do you know which Data table to use?

[mrgillus]

Ideally if the first one is selected then then you could chose in the second box which detail to show. I will upload an updated version. And, on this new version is there a way for data validation to omit the blanks from the drop down list in A2?? Thanks again for your help!

[NBVC]

I am not sure still what you are trying to do...

Can you specify exactly what you want to happen?

What is supposed to be in A3 and based on exactly what?

[mrgillus]

Once again I apologize as I know this is somewhat confusing. The scenario is this: I want a user to beable to select a something from A1 first. Lets say that want to know something about Peas then they chose peas from the drop down list in A1. Now that peas has been selected they can chose from the next drop down in A2 whether to display the Desc or the Type. Then in the merged box A3 it will show "Red in color" if from the drop down in A2 they chose "Desc" and in A3 it will show "Fruit" if they have chosen "Type" from the drop down in A2.

Let me know if that helps..

[NBVC]

Ok, I understand now, I think...

First, it is best not to work with merged cells... this is what is making your data validation include blanks...

See if the attached is suitable...

Formula in A3:

=INDEX($J$2:$L$3,MATCH(A2,$I$2:$I$3,0),MATCH(A1,Color,0))

... If you must have it as merged cells, then you should enter the list manually in Data Validation|List as: Type,Desc

Then use formula in A3:

=INDEX($J$2:$L$8,MATCH(A2,$I$2:$I$8,0),MATCH(A1,Color,0))

[mrgillus]

That works fantastic! I actually stumbled upon that method just before I got your response back. Now I am going to one-up it though. The form that I'm actually working on has more text than can fit into one cell.. In other words let's say Type also has another column called Type(continued) and I want to beable to bring that into the Cell A4 just below A3.. I have attached an update to the doc you just sent me that better demonstrates..

Thanks

[NBVC]

Try:

=IF(A2="Type",INDEX($J$4:$L$4,MATCH(A1,Color,0)),"")

in A4

[mrgillus]

That works.. Give me a few minutes to play with this.. I think I still may have to make this more complicated yet.. I think I may have to do the same thing with the Desc tab as I did with the Type tab.. e.g. there will need to be a "Desc (Continued)", which I think will cause this to conflict.. Thanks again for all your help.. I'll get back with you in a few to let you know..

[mrgillus]

OK NBVC, I think I have it.. I am just furthering out your If statement to show if false then it indexes again based on the "Desc (Continued)" row.. It seems to work great. Thanks for all your help!

Also I am attaching the copy of what I did for others to see..

[NBVC]

In case that is what you want.. check the attached... it caters for both Desc and Type with it's "continued"...

[mrgillus]

Forgot to upload file.. here it is

[NBVC]

See my last example, which would allow you to better cater to more input values (in case you have more than just Type, Desc....