Related:

- Comparing columns and returning value
- Comparing columns and returning value ✓ - Forum - Excel
- Comparing columns and returning value ✓ - Forum - Excel
- Compare values in two columns and return the value from third ✓ - Forum - Excel
- Compare Column A to Column B and return Column C ✓ - Forum - Excel
- Excel - Compare column A & B give results in column C - How-To - Excel

## 7 replies

venkat1926

- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021

SEE THE FILE FOR WHILCH WEB ADDRESS IS GSIVEN BELOW

http://speedy.sh/7EJqt/SATSY-130704.xlsx

A1,B1.C1 are having validaiton list

see the formula in E1

choose different values from validation list both in B 1 C1. E1 will change according

http://speedy.sh/7EJqt/SATSY-130704.xlsx

A1,B1.C1 are having validaiton list

see the formula in E1

choose different values from validation list both in B 1 C1. E1 will change according

SATSY

- Posts
- 4
- Registration date
- Thursday July 4, 2013
- Status
- Member
- Last seen
- July 5, 2013

Thanks for your reply. However, i would like A1 and B1 only to have validation list. C1 should indicate the result.

For example:

if A1 is 5 and B1 is E then C1 should indicate "H"

if A1 is 2 and B1 is E then C1 should indicate "M"

if A1 is 2 and B1 is B then C1 should indicate "L"

Pls help, i've been banging my head over this. Thanks.

For example:

if A1 is 5 and B1 is E then C1 should indicate "H"

if A1 is 2 and B1 is E then C1 should indicate "M"

if A1 is 2 and B1 is B then C1 should indicate "L"

Pls help, i've been banging my head over this. Thanks.

venkat1926

- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021

FIRST CLEAR c1 (CLEAR ALL0

THEN TYPE THIS FORMULA IN C1

=IF(AND(A1=5,B1="E"),"H",IF(AND(A1=2,B1="E"),"M",IF(AND(A1=2,B1="B"),"L","")))

Now choose A1 and B1 from validatgion list and if you choose the items echich you have indicated in the three choices then automatically you willl get C1 and also the counts in E1

THEN TYPE THIS FORMULA IN C1

=IF(AND(A1=5,B1="E"),"H",IF(AND(A1=2,B1="E"),"M",IF(AND(A1=2,B1="B"),"L","")))

Now choose A1 and B1 from validatgion list and if you choose the items echich you have indicated in the three choices then automatically you willl get C1 and also the counts in E1

SATSY

- Posts
- 4
- Registration date
- Thursday July 4, 2013
- Status
- Member
- Last seen
- July 5, 2013

Awesome! thanks. hey i can you fwd me ur email i can drop my excel sheet you can i see what im trying to do. in any case, this is what i want.

A and B column have validation list as below:

A B

1 A

2 B

3 C

4 D

5 E

Above validation list has following combination:

if A1 is 1 and B1 is A then C1 should indicate "L"

if A1 is 2 and B1 is A then C1 should indicate "L"

if A1 is 3 and B1 is A then C1 should indicate "L"

if A1 is 4 and B1 is A then C1 should indicate "L"

if A1 is 1 and B1 is B then C1 should indicate "L"

if A1 is 2 and B1 is B then C1 should indicate "L"

if A1 is 3 and B1 is B then C1 should indicate "L"

if A1 is 1 and B1 is C then C1 should indicate "L"

if A1 is 2 and B1 is C then C1 should indicate "L"

if A1 is 1 and B1 is D then C1 should indicate "L"

if A1 is 5 and B1 is A then C1 should indicate "M"

if A1 is 4 and B1 is B then C1 should indicate "M"

if A1 is 5 and B1 is B then C1 should indicate "M"

if A1 is 3 and B1 is C then C1 should indicate "M"

if A1 is 4 and B1 is C then C1 should indicate "M"

if A1 is 2 and B1 is D then C1 should indicate "M"

if A1 is 3 and B1 is D then C1 should indicate "M"

if A1 is 1 and B1 is E then C1 should indicate "M"

if A1 is 2 and B1 is E then C1 should indicate "M"

if A1 is 5 and B1 is C then C1 should indicate "H"

if A1 is 4 and B1 is D then C1 should indicate "H"

if A1 is 5 and B1 is D then C1 should indicate "H"

if A1 is 3 and B1 is E then C1 should indicate "H"

if A1 is 4 and B1 is E then C1 should indicate "H"

if A1 is 5 and B1 is E then C1 should indicate "H"

This combination is listed in my excel sheet as below in A,B & C column:

1 A L

2 A L

3 A L

4 A L

1 B L

2 B L

3 B L

1 C L

2 C L

1 D L

5 A M

4 B M

5 B M

3 C M

4 C M

2 D M

3 D M

1 E M

2 E M

5 C H

4 D H

5 D H

3 E H

4 E H

5 E H

Thats it! Thanks again.

A and B column have validation list as below:

A B

1 A

2 B

3 C

4 D

5 E

Above validation list has following combination:

if A1 is 1 and B1 is A then C1 should indicate "L"

if A1 is 2 and B1 is A then C1 should indicate "L"

if A1 is 3 and B1 is A then C1 should indicate "L"

if A1 is 4 and B1 is A then C1 should indicate "L"

if A1 is 1 and B1 is B then C1 should indicate "L"

if A1 is 2 and B1 is B then C1 should indicate "L"

if A1 is 3 and B1 is B then C1 should indicate "L"

if A1 is 1 and B1 is C then C1 should indicate "L"

if A1 is 2 and B1 is C then C1 should indicate "L"

if A1 is 1 and B1 is D then C1 should indicate "L"

if A1 is 5 and B1 is A then C1 should indicate "M"

if A1 is 4 and B1 is B then C1 should indicate "M"

if A1 is 5 and B1 is B then C1 should indicate "M"

if A1 is 3 and B1 is C then C1 should indicate "M"

if A1 is 4 and B1 is C then C1 should indicate "M"

if A1 is 2 and B1 is D then C1 should indicate "M"

if A1 is 3 and B1 is D then C1 should indicate "M"

if A1 is 1 and B1 is E then C1 should indicate "M"

if A1 is 2 and B1 is E then C1 should indicate "M"

if A1 is 5 and B1 is C then C1 should indicate "H"

if A1 is 4 and B1 is D then C1 should indicate "H"

if A1 is 5 and B1 is D then C1 should indicate "H"

if A1 is 3 and B1 is E then C1 should indicate "H"

if A1 is 4 and B1 is E then C1 should indicate "H"

if A1 is 5 and B1 is E then C1 should indicate "H"

This combination is listed in my excel sheet as below in A,B & C column:

1 A L

2 A L

3 A L

4 A L

1 B L

2 B L

3 B L

1 C L

2 C L

1 D L

5 A M

4 B M

5 B M

3 C M

4 C M

2 D M

3 D M

1 E M

2 E M

5 C H

4 D H

5 D H

3 E H

4 E H

5 E H

Thats it! Thanks again.

venkat1926

- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021

I wonder whether such large number of combination the formula will be so complex that you annot modify or change it. besides in some versions there is restsriction of sesven nested if(s). perhpas one can try a macro. are you comfortable with a macro

venkat

&&&&venkat1926&&&&@gmail.com

venkat

&&&&venkat1926&&&&@gmail.com

SATSY

- Posts
- 4
- Registration date
- Thursday July 4, 2013
- Status
- Member
- Last seen
- July 5, 2013

If the Macros can do the trick, then why not! will it be possible that the formulas will be automatically copied whenever i insert a new row?

- Posts
- 1864
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- August 7, 2021

yes it is possible. but I think that restriction of number nested ifs valid in macro also.

APPEARS TO BE A NEW SOLUTION

See the file given in webpage

http://speedy.sh/YqW6b/SASY-3-130706.xlsx

the data is from rows 21 down (only s ample of your data is taken to explai)

A and B data here and C formulla anad D particular letter

A1 B1 have validations. see C1 and D1 formulas.

does this serve your purpose

APPEARS TO BE A NEW SOLUTION

See the file given in webpage

http://speedy.sh/YqW6b/SASY-3-130706.xlsx

the data is from rows 21 down (only s ample of your data is taken to explai)

A and B data here and C formulla anad D particular letter

A1 B1 have validations. see C1 and D1 formulas.

does this serve your purpose