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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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))
 
Upvote 0
Hi & welcome to MrExcel.

Removed, not thinking properly. :(
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
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)
 
Upvote 0
Got it, thanks Fluff. Consider yourself redeemed!!!
Thanks for your help :)
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
@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.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top