Column title based on criteria

Hello again,

I would like to title columns based on values from other cells...

A	B	C	D	E	F	G	H	I
1	123	12	15	80%	1
2	124	22	39	56%	
3	125	22	35	63%	
4	234	12	15	80%	1
5	235	17	28	61%	
6	236	6	11	55%	
7	345	17	23	74%	1
8	346	21	33	64%	
9	347	21	32	66%

Where "1" (Column F) identifies the rows that meet the criteria (>=70%)
So...Column G (Cell G1) would be 123, H (Cell H1) would be 234, I (Cell I1) would be 345.

I know the identifier is probably not necessary, but I thought it'd be easier, but I didn't figure it out even with it.
I had a column assigned to each number, but my document ended up having like 100 columns! It would be best to only use columns for rows that meet the criteria.

Thanks!

Hi,

Try this in column F instead of column G and do it in one go

=IF(E1>=70%,B1,"")

Originally Posted by oldchippy

Hi,

Try this in column F instead of column G and do it in one go

=IF(E1>=70%,B1,"")

Thanks for the suggestion, but that still requires me to have separate columns for each row. I wanted consecutive columns to only contain what meets the criteria.
I don't know if there's a way to have the Cell G1 find the first "1" in column F, then Cell H1 find the second "1" in column F and so on...that would be the only way I can imagine doing it.

I guess this would count as finding the nth occurrence of a value or string. I used a vlookup function to return the first value, but can't figure out how to get it return second value.

I've googled "nth value excel" and there's some information out there, but in the examples I see, their problem is solved by creating a new column and adding a number association to the strings to differentiate them. Is there any other way?

Try this, if you put this formula in F1 and copy down

=IF(E1>=70%,ROW(),"")

then this in G1 copied across to the right

=IF(ISERROR(VLOOKUP(SMALL($F:$F,COLUMN()-6),$A$1:$B$9,2,FALSE)),"",VLOOKUP(SMALL($F:$F,COLUMN()-6),$A$1:$B$9,2,FALSE))

Originally Posted by oldchippy

=IF(E1>=70%,ROW(),"")
=IF(ISERROR(VLOOKUP(SMALL($F:$F,COLUMN()-6),$A$1:$B$9,2,FALSE)),"",VLOOKUP(SMALL($F:$F,COLUMN()-6),$A$1:$B$9,2,FALSE))

Awesome! Thanks a ton, oldchippy!

Hi asdvendar,

Got your message about column A, you could just select column A at the top, right click > Hide if you don't need it in view

I had this working fine, but as I moved things around, I figured out some way to screw it up!

The cells I am referencing are M256:S563, I want to display the values of the second column where the criteria of column R<>"" in cells AL1:BT1.

I know it's hard to follow, so I am attaching my Excel sheet without the guts.

I was pretty sure I was doing it right, I played around with the example and got it to work in different scenarios, but I can't get it to work when it matters...

Thanks in advance.

I managed to get it to work...somewhat...

=IF(ISERROR(VLOOKUP(SMALL($S:$S,COLUMN()-37),$M$256:$N$563,2,FALSE)),"",VLOOKUP(SMALL($S:$S,COLUMN()-37),$M$256:$N$563,2,FALSE))

However, sometimes it gives me blanks leading up to the number I want, then I have to adjust the COLUMN() correction factor.
Other times it repeats a number (like now it is repeating 47 in the Excel sheet).
And it even gives me values I don't want...

Any ideas?