Dynamic INDEX MATCH row & column

CunningFoxDemon

New Member
Joined
Jan 8, 2018
Messages
7
Hello,

I'm trying to identify the last entry in a row using INDEX,MATCH. I'm able to pull the dynamic row, but as I'm unable to have the formula identify the last column in that same row, I am forced to overwrite the data instead of adding a new date's data in a new column.

Can someone help me to correct the below formula to find a dynamic column that finds the last non-blank entry of a dynamic row?

=INDEX('Materials (V)'!$A$2:$FG,MATCH(A3,'Materials (V)'!$A$2:$A,0),2)

Materials (V)!A2 holds the item with the first value entry in B2. In other words "2" in the column variable is finding the first date's data. The 2nd date's data would be in column 3. How do I dynamically call column 3 of that specific row?

Also,
if column 3 doesn't have data in it for the specific row, but the next day it does, say column 4, how do I get it to find that value?

Thank you for your time and support. Any help is much appreciated.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
It's difficult to visualize what you're looking for, But i think I get it..

A simple match finds a row #.
You then want to do a match in THAT row #, and index another row based on that match

Right?

Something like this for example
<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Header1</td><td style=";">Header2</td><td style=";">Header3</td><td style=";">Header4</td><td style=";">Header5</td><td style="text-align: right;;"></td><td style=";">RowVal</td><td style=";">ColVal</td><td style=";">Header4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">other</td><td style="text-align: right;;"></td><td style=";">ColVal</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">stuff</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">here</td><td style=";">ColVal</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">now</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">RowVal</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">ColVal</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">not</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">this</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">ColVal</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">stuff</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I1</th><td style="text-align:left">=INDEX(<font color="Blue">B1:E1,MATCH(<font color="Red">H1,INDEX(<font color="Green">B2:E9,MATCH(<font color="Purple">G1,A2:A9,0</font>),0</font>),0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

CunningFoxDemon

New Member
Joined
Jan 8, 2018
Messages
7
Sorry, I'll illustrate below:

I want to INDEX MATCH the ItemName2 in the 2nd table with the last row cost entry of the 1st table being D3

ABCD
1MaterialsDate1Date2Date3
2ItemName1201510
3ItemName2200250
4ItemName323

<tbody>
</tbody>

ABCD
1MaterialsQuantityCostTotal
2ItemName15
3ItemName24

<tbody>
</tbody>

Here is what I have so far, but the formula does not dynamically locate the last column entry of the specific row.

=INDEX(Table1!A2:D4,MATCH(A3,Table1!A2:A,0),3)

Currently, the column variable for MATCH is manually entered. I want to make it dynamic to that specific row.

Thank you for the response!
 

CunningFoxDemon

New Member
Joined
Jan 8, 2018
Messages
7
I'm not sure how to edit my own post, but the above formula I posted should be in Table2 cell C3 to pull the cost from Table1 dynamically.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

First, this isn't a valid formula, the range is incomplete.
=INDEX(Table1!A2:D4,MATCH(A3,Table1!A2:A??,0),3)
I assume that should be
=INDEX(Table1!A2:D4,MATCH(A3,Table1!A2:A4,0),3)

Currently, the column variable for MATCH is manually entered. I want to make it dynamic to that specific row.
What are you referring to when you say 'the column variable'? The 3 ?
And It's not clear exactly 'how' you imagine that to be made dynamic, based on what?
 

CunningFoxDemon

New Member
Joined
Jan 8, 2018
Messages
7
I should note that I'm using Google Sheets.
The A2:A range is a range that starts at A2 and goes all the way down the A column.

Your assumed formula would also work in this example.

Yes, I'm referring to the 3 as the column variable of the below formula:
=INDEX(Table1!A2:D4,MATCH(A3,Table1!A2:A4,0),3)

I want the INDEX MATCH to pull the row number that matches "ItemNumber2" as well as the last column with a value entered of that same row.
In this example the row is using match to pull the row of "ItemNumber2" from Table1, but it's not pulling the last entered value of that same row dynamically.

When I say dynamic, I mean I want it to pull Table1 D3 just by looking up "ItemNumber2" in Table2 and looking to the row of that row and finding the last entered number.

Thanks again for your time. I apologize if I'm not explanation is not clear. I hope that helps.

 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

OK, I see it now..

Try this
=LOOKUP(9.99999999999999E+307,INDEX($B$2:$D$4,MATCH(A8,$A$2:$A$4,0),0))

I didn't make 2 separate sheets, both tables are on the same page.
A8 is the lookup value.

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Materials</td><td style=";">Date1</td><td style=";">Date2</td><td style=";">Date3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">ItemName1</td><td style="text-align: right;;">20</td><td style="text-align: right;;">15</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">ItemName2</td><td style="text-align: right;;">200</td><td style="text-align: right;;"></td><td style="text-align: right;;">250</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">ItemName3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Materials</td><td style=";">Quantity</td><td style=";">Cost</td><td style=";">Total</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">ItemName1</td><td style="text-align: right;;">5</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">ItemName2</td><td style="text-align: right;;">4</td><td style="text-align: right;;">250</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">ItemName3</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C8</th><td style="text-align:left">=LOOKUP(<font color="Blue">9.99999999999999E+307,INDEX(<font color="Red">$B$2:$D$4,MATCH(<font color="Green">A8,$A$2:$A$4,0</font>),0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C9</th><td style="text-align:left">=LOOKUP(<font color="Blue">9.99999999999999E+307,INDEX(<font color="Red">$B$2:$D$4,MATCH(<font color="Green">A9,$A$2:$A$4,0</font>),0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C10</th><td style="text-align:left">=LOOKUP(<font color="Blue">9.99999999999999E+307,INDEX(<font color="Red">$B$2:$D$4,MATCH(<font color="Green">A10,$A$2:$A$4,0</font>),0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

CunningFoxDemon

New Member
Joined
Jan 8, 2018
Messages
7
Hahaha, NOW WAY! That lookup method popped up during my research, but didn't think I would have to put the INDEX function inside of it as the search_range/result_array. That's great!

Thank you so much for the time and support. It means a lot! :)
I'm very excited to finally work with this method of indexing information.

Thank you!!
 

CunningFoxDemon

New Member
Joined
Jan 8, 2018
Messages
7
Would you be wiling to explain how the LOOKUP function, using the biggest calculable number, tells excel to pull the last column with data in it according to the INDEX/MATCH? How does it know to count the columns?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,271
Messages
5,635,215
Members
416,847
Latest member
inaramos

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
Top