HELP! Formula Nesting

NX555V

Board Regular
Joined
Feb 27, 2007
Messages
159
Hello All,

Thank in advance.

I have 2 dropdowns (validation list). One dropdown contains a list of "first names" 10 of them, the second contains a list of "last names" 10 of them also. When someone chooses a combination of "john" and "smith" (for example) from the individual dropdowns then cell A2 returns the value of "good student". The word "good student" is located on another sheet, I just want to point to it. Sheet2!A50 (for example). If someone chooses the combination "jane" and "wilson" then it will return the value "bad student" from another sheet.

I need to be able use the formula in 1 cell. That cell value continues to change as people choose different "first" and "last" names.

What is a good formula to accomplsih this?
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274
I am sorry but I did not understand your question thoroughly.

The drop-down list have 2 values picked up from a range. Is this range at a constant ? I mean, if the 2 ranges have values, John and Smith, are they static or do these values keep changing in the range ?
 

Gettingbetter

Well-known Member
Joined
Oct 12, 2004
Messages
602
Im guessing what your after here,

In this sum you have a validation dropdown in B5 and a validation dropdown in D5 (your first and last names). You also have a list of names in sheet2 column A, with good student, bad student etc in column B.

=IF(ISERROR(VLOOKUP(B5&" "&D5,Sheet2!A:B,2,FALSE)),"No student of that name",VLOOKUP(B5&" "&D5,Sheet2!A:B,2,FALSE))

What the formula is saying, is if theres an error finding the students name then its not in your list in sheet2, therefor put "No student of that name", if there isnt an error then it looks for the student in column A of sheet2 and brings back what is written in column B of sheet2

Hope this helps, you will obvioulsy have to change B5 and D5 to the cells that have your validation dropdown on them.

Cheers
GB
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,962
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top