Index/match top cell

H

hnorgaar

New Member
Joined
Jul 21, 2018
Messages
31
Office Version
  1. 2019
Platform
  1. Windows
Im looking to find a formula to search several rows with a cell value and return the top cell in that row, how can I do that? The cell value I try to match with will be unique
Any input is valued, thx

Henrik
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
What do you mean by the 'top cell in that row'? Can you provide a simple example of a few rows with a search term?
 
Upvote 0
Sure, I want to lookup BW and match it to the correct row from row Q to BV and as result the top cell from the row it finds it in, aka the description

Mrexcel.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBW
1KtypeVehicleIDChange DateChange CommentMakeModelSubModelEngineEngineTypeFuelTypeVehicleQualifierValvesBHPYearSpecial CommentsFront Brake Caliper MakeEBC Yellowstuff Pads and Brake Line Performance PackEBC Orangestuff Pads and Brake Line Track PackEBC Ultimax Pads and Plain Disc Front KitEBC Greenstuff Pads and Plain Disc Front KitEBC Redstuff Pads and Plain Disc Front KitEBC Yellowstuff Pads and Plain Disc Front KitEBC Bluestuff Pads and Plain Disc Front KitEBC Orangestuff Pad and Plain Disc Front KitEBC Greenstuff Pad and Ultimax Grooved Disc Front KitEBC Redstuff Pad and Ultimax Grooved Disc Front KitEBC Yellowstuff Pad and Ultimax Grooved Disc Front KitEBC Bluestuff Pad and Ultimax Grooved Disc Front KitEBC Orangestuff Pad and Ultimax Grooved Disc Front KitEBC Greenstuff Pad and Turbo Grooved Disc Front KitEBC Redstuff Pad and Turbo Grooved Disc Front KitEBC Yellowstuff Pad and Turbo Grooved Disc Front KitEBC Bluestuff Pad and Turbo Grooved Disc Front KitEBC Orangestuff Pad and Turbo Grooved Disc Front KitEBC Greenstuff Pad and BSD Disc Front KitEBC Redstuff Pad and BSD Disc Front KitEBC Yellowstuff Pad and BSD Disc Front KitEBC Bluestuff Pad and BSD Disc Front KitEBC Orangestuff Pad and BSD Disc Front KitEBC Ultimax Pad and Plain Disc Rear KitEBC Greenstuff Pad and Plain Disc Rear KitEBC Redstuff Pad and Plain Disc Rear KitEBC Yellowstuff Pad and Plain Disc Rear KitEBC Bluestuff Pad and Plain Disc Rear KitEBC Orangestuff Pad and Plain Disc Rear KitEBC Greenstuff Pad and Ultimax Grooved Disc Rear KitEBC Redstuff Pad and Ultimax Grooved Disc Rear KitEBC Yellowstuff Pad and Ultimax Grooved Disc Rear KitEBC Bluestuff Pad and Ultimax Grooved Disc Rear KitEBC Orangestuff Pad and Ultimax Grooved Disc Rear KitEBC Greenstuff Pad and Turbo Grooved Disc Rear KitEBC Reddtuff Pad and Turbo Grooved Disc Rear KitEBC Yellowstuff Pad and Turbo Grooved Disc Rear KitEBC Bluestuff Pad and Turbo Grooved Disc Rear KitEBC Orangestuff Pad and Turbo Grooved Disc Rear KitEBC Greenstuff Pad and BSD Disc Rear KitEBC Redstuff Pad and BSD Disc Rear KitEBC Yellowstuff Pad and BSD Disc Rear KitEBC Bluestuff Pad and BSD Disc Rear KitEBC Orangestuff Pad and BSD Disc Rear KitEBC Ultimax Pad and Plain Disc Full Axle KitEBC YellowStuff Pad and 2 Piece Racing Disc KitEBC YellowStuff Pad and 2 Piece Racing special Finish Disc Front KitEBC Bluestuff Pad and 2 Piece Racing Disc Front KitEBC Bluestuff Pad and 2 Piece Racing Special Finish Disc Front KitEBC RP1 Pad and 2 Piece Racing Disc Front KitEBC RP1 Pad and 2 Piece Racing Special Finish Disc Front KitEBC RPX Pad and 2 Piece Racing Disc Front KitEBC RPX Pad and 2 Piece Racing Special Finish Disc Front KitEBC Yellowstuff Pad and 2 Piece Racing Disc Rear KitEBC Bluestuff Pad and 2 Piece Racing Disc Rear KitEBC RP1 Pad and 2 Piece Racing Disc Rear KitEBC RPX Pad and 2 Piece Racing Disc Rear KitEBC Yellowstuff Pad and 2 Piece Racing Disc Full KitMerged
2119512100482ABARTH124 Spider1.4TurboPetrol1702016-2019BremboPDKF2466PD01KF1928PD11KF1346PD13KF900PDKR974PD01KR833PD03KR813PD11KR345PD13KR678PD40K2617PDKF2466
3119512100482ABARTH124 Spider1.4TurboPetrol1702016-2019BremboPDKF2466PD01KF1928PD11KF1346PD13KF900PDKR974PD01KR833PD03KR813PD11KR345PD13KR678PD40K2617PD01KF1928
4119512100482ABARTH124 Spider1.4TurboPetrol1702016-2019BremboPDKF2466PD01KF1928PD11KF1346PD13KF900PDKR974PD01KR833PD03KR813PD11KR345PD13KR678PD40K2617PD11KF1346
5119512100482ABARTH124 Spider1.4TurboPetrol1702016-2019BremboPDKF2466PD01KF1928PD11KF1346PD13KF900PDKR974PD01KR833PD03KR813PD11KR345PD13KR678PD40K2617PD13KF900
6119512100482ABARTH124 Spider1.4TurboPetrol1702016-2019BremboPDKF2466PD01KF1928PD11KF1346PD13KF900PDKR974PD01KR833PD03KR813PD11KR345PD13KR678PD40K2617PDKR974
7119512100482ABARTH124 Spider1.4TurboPetrol1702016-2019BremboPDKF2466PD01KF1928PD11KF1346PD13KF900PDKR974PD01KR833PD03KR813PD11KR345PD13KR678PD40K2617PD01KR833
8119512100482ABARTH124 Spider1.4TurboPetrol1702016-2019BremboPDKF2466PD01KF1928PD11KF1346PD13KF900PDKR974PD01KR833PD03KR813PD11KR345PD13KR678PD40K2617PD03KR813
9119512100482ABARTH124 Spider1.4TurboPetrol1702016-2019BremboPDKF2466PD01KF1928PD11KF1346PD13KF900PDKR974PD01KR833PD03KR813PD11KR345PD13KR678PD40K2617PD11KR345
10119512100482ABARTH124 Spider1.4TurboPetrol1702016-2019BremboPDKF2466PD01KF1928PD11KF1346PD13KF900PDKR974PD01KR833PD03KR813PD11KR345PD13KR678PD40K2617PD13KR678
11119512100482ABARTH124 Spider1.4TurboPetrol1702016-2019BremboPDKF2466PD01KF1928PD11KF1346PD13KF900PDKR974PD01KR833PD03KR813PD11KR345PD13KR678PD40K2617PD40K2617
12282516ABARTH5001.4TurboPetrol1352008-2011BoschPLK1001PDKF001PD01KF001PD03KF001PD06KF001PD08KF001PD11KF399PD13KF001PDKR001PD01KR001PD03KR001PD06KR001PD08KR001PD11KR160PD13KR001PD40K001PLK1001
Sheet1
 
Upvote 0
Sorry still not clear. If I search for the value in BW2 (PDKF2466) what should the result be?
 
Upvote 0
It should be the value of S1, as S2 matches the value in BW2 and row 3 should be the value in T1
 
Last edited:
Upvote 0
That looks perfect, will try it out on my big list, Thx. Appreciate it
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Looking across columns to check if cell matches to a list and return the next cell.
Replies
1
Views
194
RenD
R
U
  • Question Question
Dynamic Macro for INDEX/MATCH Formula
Replies
1
Views
365
StephenCrump
S
3
  • Question Question
Search for third instance
Replies
3
Views
106
myall_blues
myall_blues
W
  • Question Question
Index/Match Duplicate Values With No Helper Cells
Replies
3
Views
296
Domenic
D
M
  • Question Question
How to rank using index,match, small function
Replies
1
Views
231
Domenic
D

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,620
Latest member
dsubash

Share this page

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