Lookup/match question (this one is a bit involved)

[mikebroder]

ok so lets say i have a sheet that has data on it like this...

name new add up acc
bob 1 1 1 4
joe 2 1 1 3
ed 3 3 2 3


I want to be able to locate a header like name or new or add and then have it list me the items underneath it

the thing is, the original sheet gets columns added to it in various places, but the headers always stay the same...so while "new" might be in column 2 today, it may be in column 3 tomorrow

this one has been killing me...i tried a variation of index/match, but nothing is really working unless i'm missing something...and to top it off this sheet goes from A to BH as of right now and I need to find various columns throughout...

anyone up for a challenge?

 

[iliace]

You can use the INDEX function to do this:

=INDEX($A1:$Z1000, , MATCH("new", $A$1:$Z$1, 0))

By leaving the row parameter blank, you're returning the entire column. If you want to see this in a worksheet, use Ctrl+Shift+Enter because it is treated as an array formula. In this example, you will get the entire column of values that matches "new" in the first row.

Hope this helps.

 

[mikebroder]

One more monkey wrench in the works....

so when i'm referencing "new" on another sheet, i made it a shortcut so that "new" is written in cell b3....so can i simply just reference that cell or do i have to use "new" in my formula?

Also i'm referencing data from another sheet...

 

[iliace]

One more monkey wrench in the works....

so when i'm referencing "new" on another sheet, i made it a shortcut so that "new" is written in cell b3....so can i simply just reference that cell or do i have to use "new" in my formula?

You can reference anything that evaluates to "new". So yes, you can just reference your cell.

Also i'm referencing data from another sheet...

This shouldn't be a problem, but post back with the specifics if it isn't working.

 

[Peter_SSs]

Mike, welcome to the MrExcel board!

One thing to watch out for is INDEX returning zero values when the target cell is in fact blank. My guess is that something like this might be what you need.

Here's the sheet with the main data.

Excel Workbook
	A	B	C	D	E	F
1	name	new	add	up	acc
2	bob	1	1	1	4
3	joe	2	1	1	3*
4	ed	3	3	2	3
5
Data Table

And here's the sheet where we are extracting the column. Formula in B4 is copied down as far as you might ever need.

Excel Workbook
	B
3	add
4	1
5	1
6	3
7
8
Extract Column