Index/Match Based on given row value

[smeye011]

Hello,

I'm looking to use a combination of index and match to do the following:

Cell B1 contains a row number that is editable. I have it so once the row number is entered, cell B2 returns the first value completed in the given row. Now, I just need cell B10 to return the 'week' that the first value entered shows up. Photo example below -- thanks in advance!

 

[smeye011]

 

[Fluff]

How about
=MATCH(B2,INDEX(B1:E7,B1,0),0)

 

[smeye011]

Thanks!

I'm not sure if this would work if there were multiple other entries with the number 4 in the data set. I'm looking to return a specific value from row 4 in this example when the first cell is not empty in the given row (entered in cell B1).

 

[Fluff]

If B2 is the first value in the row, then it will work

 

[smeye011]

I see - I was too vague in my initial question. I'm actually working across two different tabs. The first tab has the row #, first cell value, and the cell I want to be completed with "first week active". Then the tab 'Volume Sheet' contains the week #'s across row 1 and actual rows of data to be parsed. Sorry for the confusion!

 

[Fluff]

In that case you will need to supply an accurate data layout, rather than something that has no relevance to your needs.
There are some add-ins available to enable you to post data to the thread here. https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[smeye011]

Just trying to simplify - whoops!

I'm looking to have cell E19 reference cell D6 (row number for reference tab, 'Volume Sheet') as well as cell E18 (the first value in the given row in the reference tab), and return a "weeks remaining value" (row 1 in the reference tab).

The first screenshot shows the summary sheet I'm working from, and the second screen shot contains the referenced tab. So in this case, I'd like the number 52 to return in cell E19 of the summary sheet.

Thanks again for any help!

 

[Fluff]

In that case try it like
=INDEX(B1:E1,MATCH(B2,INDEX(B1:E7,B1,0),0))
You'll need to change the ranges & add sheet names to suit

 

[smeye011]

That worked -- thanks for the help!!