how do i get a formula to look along a line

[tonylpcs@yahoo.co.uk]

Hi Everyone,

need a formula that can look at aline and indentify where the number is

i have a 20 line squire chart

along the top are first names and going down are second names, i need a formula that can look along the line (say line 10 cells D10 to D30) and find the cell that matches number 1 the take the name from the cell above it and put it with the name in cell C10 so i end up with one name!

any ideas?

thanks

Tony

 

[Peter_SSs]

Can you please clarify? To me, looking "along a line" would imply looking along a row. However, you then talk about looking at D10 to D30 which is part of a column.

Any chance of a small set of sample data and expected results?

 

[tonylpcs@yahoo.co.uk]

Hi Peter,

sorry my fault,

let me try again

for example

on line 5 from Cell D5 to Cell Y5 each cell holds a different persons first name
and running down from C6 to C26 each cell holds a sirname.
now in all the cells between d5 and y26 each cell has a number

what i want to do is lets say i z6 for example get a formula to look for the cell in that row (C6 to y6) that equals the number i put in cell D3 and when it finds the cell that has that value give me the first name and the sirname like a gid rifference lookup

hoped that helped?

tony

 

[Peter_SSs]

I assume that the numbers in the grid only occur once each. Here is a small example that I think you should be able to adapt to your larger range.

Excel Workbook
	B	C	D	E	F	G	H	I
2	Number of interest	12
3	Name	Jen Jones
4
5			Jo	Tom	Ann	Jen	Sue
6		Smith	6	7	11	14	9
7		Jones	5	3	4	12	13
8		Brown	8	2	1	10	15
9
Lookup Name

 

[tonylpcs@yahoo.co.uk]

thank you peter,
that should help,

thank you

Tony

 

[indiantrix]

Put this into X6 and copy down.
=IFERROR(OFFSET($B6,-ROW()+5,MATCH($B$3,$B6:$W6,0)-1)&" "&A6,"")
Larry.

 

[Peter_SSs]

Put this into X6 and copy down.
=IFERROR(OFFSET($B6,-ROW()+5,MATCH($B$3,$B6:$W6,0)-1)&" "&A6,"")
Larry.

Larry

If the objective is to get the name shown against the correct row (and you may very well be right) then I would suggest this alternative for X6 (copied down). I have used the same column references as you, though I don't think they match the OP's columns.

=IFERROR(INDEX(B$5:W$5,MATCH(B$3,B6:W6,0))& " "&A6,"")

The reasons for suggesting this alternative are:

1. OFFSET is a volatile function so I generally try to avoid it (especially if there are easy alternatives).

2. Your use of -ROW()+5 in the formula makes it not very robust. For example, if the user later decides they need a new row above the table we are dealing with, the formula will no longer work correctly (try inserting a new row 1).


Note that this formula still requires Excel 2007 or later. If an earlier version is being used, my suggestion would be:

=IF(COUNTIF(B6:W6,B$3),INDEX(B$5:W$5,MATCH(B$3,B6:W6,0))&" "&A6,"")

 

[tonylpcs@yahoo.co.uk]

hi peter,

thank you very much this is doing the job perfectly, and i managed to work out how to move it down to do the same on other lines no problem.

thanks

tony

 

[indiantrix]

Thanks, Peter, for the feedback. Glad to contribute, Tony!
Larry.

 

[Peter_SSs]

hi peter,

thank you very much this is doing the job perfectly, and i managed to work out how to move it down to do the same on other lines no problem.

thanks

tony

Glad you got what you wanted. As a matter of interest, were you after the name on the corresponding line like Larry suggested or just in a single cell somewhere like I had suggested in post #4?