find a value on the same row, return the cell to the left of it!

[ellison]

Hi all, I'm after a formula to search for a string of text in range which is in the same row, and return the value of the cell to the left of where that value has been found.

So for example cell A contain Names, then row B is where I'd like my "goal" to be, then columns C to G is the range of values that I'd like to use to find my goal.

i.e.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Names
[/TD]
[TD]Goal
[/TD]
[TD]Col C
[/TD]
[TD]Col D
[/TD]
[TD]Col E
[/TD]
[TD]Col E
[/TD]
[TD]Col F
[/TD]
[TD]Col G
[/TD]
[/TR]
[TR]
[TD]David
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]terry
[/TD]
[TD]2
[/TD]
[TD]David
[/TD]
[TD]66
[/TD]
[TD]anthony
[/TD]
[/TR]
[TR]
[TD]Peter
[/TD]
[TD]67
[/TD]
[TD]11
[/TD]
[TD]john
[/TD]
[TD]36
[/TD]
[TD]james
[/TD]
[TD]67
[/TD]
[TD]PETER
[/TD]
[/TR]
</tbody>[/TABLE]

(I'd like to use a formula - as opposed to a macro. And I'd like the search to "not" be case sensistive

I've found a few pointers to do with indexes, matches and offsets, but I'm really struggling!

Best Regards
Neil

 

[jtakw]

Hi,

From Column C on, can the same Name appear more than once?

BTW, you have TWO column E in your sample.

 

[Fluff]

Maybe

Excel 2013 32 bit
	A	B	C	D	E	F	G	H
1	Names	Goal	Col C	Col D	Col E	Col E	Col F	Col G
2	David	2	1	terry	2	David	66	anthony
3	Peter	67	11	john	36	james	67	PETER
Sheet1

Cell Formulas
Range	Formula
B2	=INDEX(C2:H2,,MATCH(A2,C2:H2,0)-1)

 

[ellison]

Crumbs, that's absolutely brilliant. My head will be eternally grateful as I can stop scratching it!

 

[Fluff]

Glad we could help & thanks for the feedback.

BTW If you have two or cells with the same name from col C onwards this will simply take the first name.

 

[jtakw]

Since you ignored my question from Post #2 , how about this simple formula using just SUMIF:

Book1
	A	B	C	D	E	F	G	H
1	Names
2	David	2	1	terry	2	David	66	anthony
3	Peter	67	11	john	36	james	67	PETER
Sheet68

Cell Formulas
Range	Formula
B2	=SUMIF(D2:H2,A2,C2:G2)

Formula copied down.

 

[ellison]

many thanks - and many apologies, wasn't ignoring. All help very much appreciated!

 

[jtakw]

No problem at all, you're very welcome.

SUMIF is the simplest way to go based on your current data.