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

dregsy

New Member
Joined
Sep 30, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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".

1601487407533.png


Any help anyone can offer would be greatly appreciated.

Thanks.
 

Some videos you may like

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
Joined
Aug 18, 2015
Messages
10,332
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
Joined
Jun 12, 2014
Messages
47,960
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.

Removed, not thinking properly. :(
 

dregsy

New Member
Joined
Sep 30, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 30, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Li
Hi & welcome to MrExcel.
How about
+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
Joined
Jun 12, 2014
Messages
47,960
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 30, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Got it, thanks Fluff. Consider yourself redeemed!!!
Thanks for your help :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,960
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,960
Office Version
  1. 365
Platform
  1. Windows
@XcelXpert
Please post your solution to the board.
As per rule#4
please do not answer questions by creating solutions elsewhere and then referencing those solutions via file links.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,995
Messages
5,543,183
Members
410,584
Latest member
Bluefox68
Top