HELP! Formula Nesting

NX555V

Board Regular
Hello All,

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?

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Stormseed

Banned
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
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

Replies
6
Views
213
Replies
3
Views
279
Replies
9
Views
270
Replies
7
Views
367
Replies
1
Views
530

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,982
Messages
5,834,703
Members
430,310
Latest member
Excelorate3305

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.

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

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