Is this a lookup function of sorts? Where do I start?

[AFST]

I'm sure there's a very easy answer to this, I've just never had to do it before and don't really know where to start. I have a cell with a drop down list of names in it (call it the name cell). What I would like is a formula that evaluates a column for a matching name based on whats in the "name cell" and it returns all the info in the same row as the met criteria. for example:

A B C
1 Gary Red Large
2 Scott Blue Medium
3 Bill Green Small

So if I had selected Scott in the "name cell" I'm looking for it to return:

Scott Blue Medium

Thanks for any help on this one guys

 

[jeffreybrown]

This might help...

http://www.contextures.com/xlfunctions02.html

 

[scottylad2]

A vlookup will do as you need as long as your lookup data is stored as per the example

Excel Workbook
	A	B	C
1	Data Table
2	Gary	Red	Large
3	Scott	Blue	Medium
4	Bill	Green	Small
5
6	Drop Down	Colour	Size
7	Scott	Blue	Medium
Sheet4

Excel Workbook
	C
7	Medium
Sheet4

 

[AFST]

That kind of works for me but in my case I have NUMEROUS columns (column 1 is merged cells from A:F and then were is many columns that contain the data I want returned G:GD) So yes this works, but it would take me forever to make that up. Is there a simpler way?

 

[jeffreybrown]

Well the first trouble you are going to run into is those merged cells. If you can ditch them, ditch them now before you get to far.

If I understand you, you can use this modification to Deek suggestion to change the third argument dynamically.

=VLOOKUP($A7,$A$2:$C$4,COLUMNS($A$1:B2),0)

 

[scottylad2]

That kind of works for me but in my case I have NUMEROUS columns (column 1 is merged cells from A:F and then were is many columns that contain the data I want returned G:GD) So yes this works, but it would take me forever to make that up. Is there a simpler way?

although all the columns are merged, you would be better to unmerge them and from that i would guess you'll be left with a single column of data you can use for the Vlookup. If you need to give the appearance of merged cells,then highlight the cell with the data and all the cells that were previously merged. Then gointo the Format cells dialogue box and under the Alignment tab, selecr Centre Across Selection. You can then go into the Borders tab and remove borders for the same cells and the appearance of the merged cells will still remain, but your vlookup criteria will be in a single cell. As jeff pointed out, ucing the Columns in the column argument of the Vlookup will mean once you have written the formula in one cell you can then just drag to all the other that have info

 

[AFST]

OK I've taken this info and I've managed to produce the result I'm looking for in first part of my worksheet. So a huge thanks for that. Now I have a similar but different issue.

I have a table spanning from A1:R100 on sheet 1. In columns J:Q there are members names. One person's name can appear in any one of those columns. On sheet 2 is there a way to make a formula that checks those columns for a name (provided in reference cell A1 on sheet2) and then returns in the info from all of the columns A:R in the same row as where the name was found. Lets say the result would appear in A2:R2 on sheet 2.

And then to further complicate the process can the formula be copied or modified so that in row 3 it shows the next occurrence of that name without repeating whats in row 2 etc.

Its basically a lot like a filter but its checks multiple columns and will eventually need to check multiple worksheets but compile on the info onto one worksheet.

I hope this makes sense and somebody can lend a hand. I'm at a total loss

 

[jeffreybrown]

I don't know how/where to start with this question, so it would help if you posted a sample workbook to a place like box.net and provide the link here.

 

[AFST]

I tried that site, and unfortunately I'm in a location that doesn't have a good connection at all. I tried some screen shots of a sample I threw together, and hopefully with a bit a explanation it could help.


-- removed inline image ---



Ok so that first shot is where I want the result. In the cell next to the "look up who?" is a drop down list of the names of people. What I would like to happen is when I choose a name from the box, the member's log section is auto populated from other sheets in the same work book.

The second shot is a sample of one of the sheets that needs to be checked. Each sheet is just a month of the year. The persons name could appear in any one of the "ACM" columns that are on the right but it will only appear once in any given row, if it appears at all.

So ideally when I pick a name from screenshot 1 it will check all the other worksheets for where that name appears and duplicate all the info thats in the same row as the name. The end goal is that I see every time this persons name shows up and all the details associated with that occurence.

Did that help at all?

 

[AFST]

Nuts! My screen shots did not post. I'm at a total loss now, suggestions? The only thing I can think of is an e-mail but I doubt anybody would be interested in that.
-- removed inline image ---