Get text in cell above another certain cell...

[Dan1000]

Hello

<TABLE style="WIDTH: 225pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=299 x:str><COLGROUP><COL style="WIDTH: 106pt; mso-width-source: userset; mso-width-alt: 5156" width=141><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5778" width=158><TBODY><TR style="HEIGHT: 38.25pt; mso-height-source: userset" height=51><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 106pt; HEIGHT: 38.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=51 width=141>Text to get 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 119pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=158>Text to get 2</TD></TR><TR style="HEIGHT: 38.25pt; mso-height-source: userset" height=51><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 106pt; HEIGHT: 38.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=51 width=141>Recognise text 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 119pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 width=158>Recognise text 2</TD></TR><TR style="HEIGHT: 38.25pt; mso-height-source: userset" height=51><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 38.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=51>Text to get 3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>Text to get 4</TD></TR><TR style="HEIGHT: 38.25pt; mso-height-source: userset" height=51><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 106pt; HEIGHT: 38.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 height=51 width=141>Recognise text 3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 119pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 width=158>Recognise text 4</TD></TR></TBODY></TABLE>

Is there a formula (vlookup perhaps) which will find one of the Recognise... text strings in the array (sample above) and return the text in the cell immediately above if the find text matches.

Kind regards

 

[VoG]

Hi. Perhaps like this

Excel Workbook
	A	B	C	D
1	Text to get 1	Text to get 2		Text to get 3
2	Recognise text 1	Recognise text 2
3	Text to get 3	Text to get 4
4	Recognise text 3	Recognise text 4
Sheet2

 

[energman58]

If your arrray is small you can do it using INDEX and Match like this example for a two column array:

=IF(ISNA(INDEX(B18:B21,MATCH(E16,B18:B21,0)-1)),IF(ISNA(INDEX(C18:C21,MATCH(E16,C18:C21,0)-1)),"Not Found",(INDEX(C18:C21,MATCH(E16,C18:C21,0)-1))),(INDEX(B18:B21,MATCH(E16,B18:B21,0)-1)))

E16 has the value you are looking for B18:C21 has your table

It returns "Not Found" if your lookup value isnt in the table

If your array is much bigger you might need to write some code to loop through the columns as you will hit constraints on nesting IFs and it will get horrible to debug anyway

 

[Dan1000]

Thanks VoG.

That works for one column (f:f in your formula) but when I change to an array (f2:G5) it does not work??? I need the formula to check this array(f2:G5).

Regards

 

[T. Valko]

Thanks VoG.

That works for one column (f:f in your formula) but when I change to an array (f2:G5) it does not work??? I need the formula to check this array(f2:G5).

Regards

Try this array formula**.

=OFFSET(F2,MAX(IF(F2:G5=A15,ROW(F2:G5)-ROW(F2)+1))-2,MAX(IF(F2:G5=A15,COLUMN(F2:G5)-COLUMN(F2))))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[Dan1000]

Just discovered another problem. Two or more of the lower cells are / may be the same. Is there a way of accounting for that? ie just ignore the additional ones so it accounts for one of each unique value only or work it out two+ times.

I would like to have a count of the occurences of each but this can be a separate formula in another cell

Regards

 

[T. Valko]

Just discovered another problem. Two or more of the lower cells are / may be the same. Is there a way of accounting for that? ie just ignore the additional ones so it accounts for one of each unique value only or work it out two+ times.

I would like to have a count of the occurences of each but this can be a separate formula in another cell

Regards

I'm not sure what you're asking but you're mentioning that there are duplicates gives me a headache just thinking about it!