using lookup formula

[morand]

Hi,
Just wondering if anyone could figure how I can do the following:

I have 2 columns below, One named School and the other one called Area.

My formula in the school column is: =LOOKUP($B7,{"management","hairdressing","medicine"},{"Management School","Hairdressing School","have to do further research"})

However, the formula works fine, except for when it comes to 'Medicine'. When it finds medicine, I want it to check another cell in the same row and then check another excel sheet table to check for the answer. I was looking at nested if functions etc but Maybe this is too complicated for a formula?

any help, really appreciated?

School	                                        Area
Hairdressing School	                        management
Hairdressing School	                        hairdressing
have to do further research	                medicine
Hairdressing School	                        management
Hairdressing School	                        hairdressing

 

[VBA Geek]

heads up when you use the lookup function, the lookup_vector has to be a sorted list, in your formula it is not sorted

LOOKUP($B7,{"management","hairdressing","medicine"},{"Management School","Hairdressing School","have to do further research"})

 

[morand]

Hi VBAGeek,

Yes, I understand that. But the problem is that if B2 Cell is equal to 'medicine', I don't want it to return 'have to do further research'. Instead I want it to look up another cell and find an answer? Does that make sense. Maybe there is a better way of doing it than the lookup function? the lookup function works find for the other values except 'medicine'...

heads up when you use the lookup function, the lookup_vector has to be a sorted list, in your formula it is not sorted

LOOKUP($B7,{"management","hairdressing","medicine"},{"Management School","Hairdressing School","have to do further research"})

 

[Joyner]

Hello morand and welcome to the board.

I'm not sure we will be able to figure out what you need from the information you have provided - we will need a bit more information. If you can, post a sample of your data and layout and your expected results. Like what values are in B7, since it doesn't look like your data range you show goes to B7. What do you want to return, you say "look up another cell and find an answer", what cell and what answer/results. You need to have some specifics and parameters etc., to get a solution.

Thanks