Formula to Lookup Based on Value and Return Data Based on Value

[twilley259]

Hello,
I am needing help with a complex lookup formula. I have a table of data with information in it shown below.

[/URL][/IMG]

So on sheet 1. I have a drop down with all the work cells listed. Whenever I select the title from the drop down, I want a formula to find that title in Row 1 then look in that column for the X's I have placed. These X's show which work instructions pertain to that job. Any field with an X I want the formula to return the hyperlink in column A. I hope this makes sense. Basically, you make a selection, excel finds that title, then if there is an X in that column it returns the corresponding A value.

[/URL][/IMG]

 

[oldbrewer]

I have a drop down with all the work cells listed.

where is the drop dpwn the printing is very small - define all work cells

 

[twilley259]

I have a drop down with all the work cells listed.

where is the drop dpwn the printing is very small - define all work cells

The drop down where you make a selection is located on sheet 1. In my example it correlates with Column P in the first photo.

 

[twilley259]

Nobody
has
any
help
on
this?

 

[Gerald Higgins]

...I want a formula to find that title in Row 1...

Row 1 seems to be pretty much blank in your picture.

Help us out here - what are the possible input values for the drop down box ?
What do you want to check them against ?
What should the result be ?

 

[oldbrewer]

preferably a few rows pasted in the reply box - once your needs are precisely defined, a solution will be upcoming............

 

[twilley259]

Row 1 seems to be pretty much blank in your picture.

Help us out here - what are the possible input values for the drop down box ?
What do you want to check them against ?
What should the result be ?

I was mistaken in my original post. Here's what I want
I want to hide the sheet titled WI
I want to use the dropdown box on sheet 1 to select a title from row 2 of the WI sheet.
When a selection is made in the dropdown I want excel to look in sheet WI P2:Z2 and find a match.
Then I want it to look for any row in the matching column that has an X in it.
Any row it finds in that column with an X I want it to return the corresponding data from WI sheet Columns A, B, C

Basically the Sheet WI is a table of data for a ton of different things. I want Sheet 1 to act as a filter in a sense and only return the data needed for the title selected.

 

[Gerald Higgins]

It might not matter too much, but you've said to look in sheet WI, P2:Z2.
In your picture, we can only see about half of the range P2:Z2, but what we can see looks pretty blank.

But let's just imagine what kind of values might possibly be found there.
I can then imagine how we might look down that column to find a row containing an X.
Is it POSSIBLE that there could be more than one row in that column containing an X ?
If YES, what do you want to do about that ?
If you just want to deal with the first X that is found, that's straightforward.
If you want something else, please explain clearly.

 

[twilley259]

It might not matter too much, but you've said to look in sheet WI, P2:Z2.
In your picture, we can only see about half of the range P2:Z2, but what we can see looks pretty blank.

But let's just imagine what kind of values might possibly be found there.
I can then imagine how we might look down that column to find a row containing an X.
Is it POSSIBLE that there could be more than one row in that column containing an X ?
If YES, what do you want to do about that ?
If you just want to deal with the first X that is found, that's straightforward.
If you want something else, please explain clearly.

The area you ask about appears blank because it is. Shouldn't affect the formula though. I'd rather get it figured out and working before spending hours populating data that I can't use. Yes there will be more than one row in a column with an X. I want the formula to report columns A,B,C in ALL rows containing an X.

 

[Gerald Higgins]

The area you ask about appears blank because it is.

Well that's helpful.

Shouldn't affect the formula though.

Might do, depending on what type of data will be in the blank area, how it's laid out, and so on.
Even if not, it will help you get a better answer if you can be super clear about your requirements, and not leave it to other people to guess what your data will look like in the future.

I'd rather get it figured out and working before spending hours populating data that I can't use.

Fair enough, but it needn't take hours to populate 11 cells with typical examples of what the data might look like.

Yes there will be more than one row in a column with an X. I want the formula to report columns A,B,C in ALL rows containing an X.

OK, so what do you want this to look like exactly, because I'm not clear ?
Give us an example please, one which has multiple rows with X, and show us what the results should be.