Picking a cell that is next to a cell

[Chris]

I have a worksheet: Column A lists the letters of the alphabet, column b is a
series of text data and column c is numerical data. i.e.

A B C
A di yes
B iw no
C ic maybe

I have set up validation lists where a user can choose on column A then the
next validation list only shows the choices of column b that correspond to
column a. i need then a cell to display the value in column c. The logic
goes for the final cell, where choice A = C and choice B = IC then display
"maybe". Any suggestions?

 

[T. Valko]

Sounds like you need a lookup table. You need to create a 3 column table
like your example that lists all the possible combinations. Assume this
table is in the range H1:J10

........H......I.......J
1.... A di yes
2.... B iw no
3.... C ic maybe

Then, assuming your 2 drop downs are in A1 and B1

Try this array formula** to get your result:

=INDEX(J1:J10,MATCH(1,(H1:H10=A1)*(I1:I10=B1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Wigi]

Hi

If the "rows" are unique, you could use the DGET function.

There's a nice example in the helpfiles on this function and other similar
database functions.

 

[Chris]

That worked great T. Thanks a ton!

Sounds like you need a lookup table. You need to create a 3 column table
like your example that lists all the possible combinations. Assume this
table is in the range H1:J10

........H......I.......J
1.... A di yes
2.... B iw no
3.... C ic maybe

Then, assuming your 2 drop downs are in A1 and B1

Try this array formula** to get your result:

=INDEX(J1:J10,MATCH(1,(H1:H10=A1)*(I1:I10=B1),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[T. Valko]

You're welcome. Thanks for the feedback!