Excel data validation comparing rows and columns

Question

Hi,

I'm trying to set value in cell from two data validation cells.

For example

sheet 1

Column A - Device type, Column - B addon 1, Column - C addon 2, so on.

So in sheet two I have;

Data Validation from sheet 1 column A, row 2-20 in column A, and data Validation from sheet1 column b and column c.. in column B.

I would like to have automatically set data in column C, addon, from row depending on data of selected columns B-C_D..

Example;

Device type        Addon 1    Addon 2     Addon 3

Car                     wheel         tyre            seatbelt

If car and addon 2 is choose in second sheet in columns A and B, cell in column C is showing TYRE.

Thanks for help

Answer 1

Hi KikiMOB,

We can set the data validation based on column A in sheet 1 but seems we can't set the validation across two different columns.

However, regarding your requirement, you may use a formula to check whether it can meet your requirement.

1. Set the data validation for column A and B
2. Use the following formula to retrieve the data when two conditions matched:

=INDEX(Sheet1!C:C,MATCH(A1&B1,Sheet1!A:A&Sheet1!B:B,0))

If this is not your scenario, please share a snippet example to let us know what you want to do.

Regards,

William

Answer 2

Hi,

thanks, this was very helpfull.

I can use this.