# Looking up a value in a row and returning the adjacent cell value.

#### dregsy

##### New Member
Hi,

I would like to be able to lookup a value in a row and return the contents of the cell to the right of it.
For example below, I would like cell D3 to show as 3 (which I think it will), but what I can't work out is how I can get E3 to return "Fred".

Any help anyone can offer would be greatly appreciated.

Thanks.

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Eric W

##### MrExcel MVP
Welcome to the MrExcel forum!

Try:

Book1 (version 1).xlsb
ABCDEFGHIJK
1
2
33Fred1Dave3Fred2Steve
Sheet12
Cell Formulas
RangeFormula
D3D3=MAX(F3:K3)
E3E3=INDEX(G3:L3,MATCH(D3,F3:K3,0))

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.

Removed, not thinking properly.

#### dregsy

##### New Member
Wow thanks Eric!!!
Can I just ask so that i understand it, why is the index range G to L and not F to K?

#### dregsy

##### New Member

Li
Hi & welcome to MrExcel.
+Fluff New.xlsm
DEFGHIJK
1
2
33Fred1Dave3Fred2Steve
4
Main
Cell Formulas
RangeFormula
D3D3=MAX(F3:K3)
E3E3=INDEX(G3:L3,D3)
Likewise Fluff, thanks for the quick reply. Without wishing to sound like i'm on Blind Date, "Same question to number 2" please!

#### Fluff

##### MrExcel MVP, Moderator
My suggestion was wrong, hence I removed it. Whilst it works with that data it would normally fail.
Because you want want 1 column to the right you can either offset the columns by one (ie G:L) or you can use F:K & add one to the match
Excel Formula:
``=INDEX(F3:K3,MATCH(D3,F3:K3,0)+1)``

#### dregsy

##### New Member

Got it, thanks Fluff. Consider yourself redeemed!!!

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

#### Fluff

##### MrExcel MVP, Moderator
@XcelXpert
As per rule#4
please do not answer questions by creating solutions elsewhere and then referencing those solutions via file links.

Replies
4
Views
76
Replies
5
Views
87
Replies
8
Views
84
Replies
2
Views
434
Replies
1
Views
171