•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Vlookup / Index disappearing
Our take
It sounds like you're encountering a common challenge with Excel's VLOOKUP and INDEX functions, especially in a macro-enabled workbook. When formulas disappear and leave only the last found value, it's often due to the way the formulas are set up or how data is being updated. Rather than manually rewriting formulas each time you modify your list, consider checking your formula references and ensuring they are set to dynamically adjust with new entries.
Apologies if I'm asking a really dumb question, I am somewhat new to excel.
I'm having a problem with a macro-enabled workbook. I have tried both Index and Vlookup, but both formulas just disappear and only leave behind the last value they found. Attached is a massively simplified (and ugly) version of what the spreadsheet is supposed to do.
The issue I'm having is that every time I add anything new into the empty slots of column B, Vlookup completely disappears from the cell it's in and just leaves behind "Item B" or whatever the last value it searched for was. Is this supposed to work this way? I also tried Index, and the result was the same. I don't know how to figure this out without manually re-writing the formula every time the list of items changes, which is a huge pain.
Thanks for any help.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Xlookup reference is shifting over each time after I use MacroHey everyone, I have my main table where I want to display the cleaned up data. Essentially I'm using xlookup references from sheet two. When I run my macro in sheet two, for some reason my xlookup on sheet 1 moves over every single time. For example... this is what the xlookup formula looks at before vs after the Macro Before Macro - =XLOOKUP(A3,MTD!$A$3:$A$15,MTD!$B$3:$H$15) After Macro - =XLOOKUP(A3,MTD!$A$3:$A$15,MTD!$B$3:$F$15) Why would it move from H15 to F15? I'm a little lost why that would be. Sub Macro5() ' ' Macro5 Macro ' ' Range("A2").Select ActiveCell.FormulaR1C1 = "Market" Range("B2").Select ActiveCell.FormulaR1C1 = "Date" Range("E2:J16").Select Selection.Cut Range("C2").Select ActiveSheet.Paste Range("A2:H2").Select Selection.Copy Range("A19").Select ActiveSheet.Paste Windows("Overview Bullet-MTD.csv").Activate Application.Left = 1551.25 Application.Top = 147.25 Windows("Weekly Exec Updates - Lead Pacing (Working File) (1).xlsx").Activate Range("A20").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "MO" Range("A21").Select ActiveCell.FormulaR1C1 = "LA" Range("A22").Select ActiveCell.FormulaR1C1 = "AR" Range("A23").Select ActiveCell.FormulaR1C1 = "MS" Range("B20").Select ActiveCell.Formula2R1C1 = _ "=XLOOKUP(RC[-1],R[-17]C[-1]:R[-4]C[-1],R[-17]C:R[-4]C[6])" Range("B20").Select Selection.AutoFill Destination:=Range("B20:B23"), Type:=xlFillDefault Range("B20:B23").Select Range("B21").Select ActiveCell.Formula2R1C1 = _ "=XLOOKUP(RC[-1],R[-18]C[-1]:R[-5]C[-1],R[-18]C:R[-5]C[6])" Range("B22").Select ActiveCell.Formula2R1C1 = _ "=XLOOKUP(RC[-1],R[-19]C[-1]:R[-6]C[-1],R[-19]C:R[-6]C[6])" Range("B23").Select ActiveCell.Formula2R1C1 = _ "=XLOOKUP(RC[-1],R[-20]C[-1]:R[-7]C[-1],R[-20]C:R[-7]C[6])" Range("B3:B16").Select Selection.NumberFormat = "m/d/yyyy" Range("E3:E16").Select Selection.NumberFormat = "0.0%" Selection.NumberFormat = "0%" Range("G3:G16").Select Selection.NumberFormat = "0.0%" Selection.NumberFormat = "0%" Range("C20:D23").Select Selection.NumberFormat = "#,##0" Range("F20:F23").Select Selection.NumberFormat = "#,##0" Range("G20:G23").Select Selection.Style = "Percent" Sheets("YTD").Select Windows("Overview Bullet-YTD.csv").Activate Windows("Weekly Exec Updates - Lead Pacing (Working File) (1).xlsx").Activate ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 Range("M2:M16").Select Selection.Cut ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Range("D2").Select ActiveSheet.Paste Range("D2").Select ActiveCell.FormulaR1C1 = "Actual Leads" Range("K2:K16").Select Selection.Cut Range("E2").Select ActiveSheet.Paste ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 Range("L2:L16").Select Selection.Cut Range("F2").Select ActiveSheet.Paste Range("H2:H16").Select Selection.Cut Range("G2").Select ActiveSheet.Paste Range("I2:V17").Select Selection.ClearContents ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Range("A2:G2").Select Selection.Copy Range("A22").Select ActiveSheet.Paste Range("A23").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "MO" Range("A24").Select ActiveCell.FormulaR1C1 = "LA" Range("A25").Select ActiveCell.FormulaR1C1 = "AR" Range("A26").Select ActiveCell.FormulaR1C1 = "MS" Range("B23").Select ActiveCell.Formula2R1C1 = _ "=XLOOKUP(RC[-1],R[-20]C[-1]:R[-7]C[-1],R[-20]C:R[-7]C[5])" Range("B23").Select Selection.AutoFill Destination:=Range("B23:B26"), Type:=xlFillDefault Range("B23:B26").Select Range("B24").Select ActiveCell.Formula2R1C1 = _ "=XLOOKUP(RC[-1],R[-21]C[-1]:R[-8]C[-1],R[-21]C:R[-8]C[5])" Range("B25").Select ActiveCell.Formula2R1C1 = _ "=XLOOKUP(RC[-1],R[-20]C[-1]:R[-7]C[-1],R[-20]C:R[-7]C[5])" Range("B25").Select ActiveCell.Formula2R1C1 = _ "=XLOOKUP(RC[-1],R[-22]C[-1]:R[-9]C[-1],R[-22]C:R[-9]C[5])" Range("B26").Select ActiveCell.Formula2R1C1 = _ "=XLOOKUP(RC[-1],R[-23]C[-1]:R[-10]C[-1],R[-23]C:R[-10]C[5])" Range("A2:G16").Select Selection.AutoFilter ActiveSheet.Range("$A$2:$G$16").AutoFilter Field:=1, Criteria1:=Array("AL", _ "CA", "HI", "NM", "NNE", "OHWVKY", "PA", "TW", "TX"), Operator:=xlFilterValues Range("A3").Select ActiveWorkbook.Worksheets("YTD").AutoFilter.Sort.SortFields.Clear ActiveWorkbook.Worksheets("YTD").AutoFilter.Sort.SortFields.Add2 Key:=Range( _ "A2:A16"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("YTD").AutoFilter.Sort .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Sheets("MTD").Select Range("A2:H16").Select Selection.AutoFilter Range("A3").Select ActiveSheet.Range("$A$2:$H$16").AutoFilter Field:=1, Criteria1:=Array("AL", _ "CA", "HI", "NM", "NNE", "OHWVKY", "PA", "TW", "TX"), Operator:=xlFilterValues ActiveWorkbook.Worksheets("MTD").AutoFilter.Sort.SortFields.Clear ActiveWorkbook.Worksheets("MTD").AutoFilter.Sort.SortFields.Add2 Key:=Range( _ "A2:A16"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal With ActiveWorkbook.Worksheets("MTD").AutoFilter.Sort .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub submitted by /u/_mavricks [link] [comments]
- Vlookup not updating until filter is clearedVlookup not updating until filter is cleared. The filter is being applied to the range with the cell containing the vlookup formula. The vlookup was returning from a row which was deleted, which should then render a N/A result. However, the N/A would not appear until the filter was cleared. In fact I entered several arbitrary numbers for the vlookup to find, some which were not in the dataset, and it still returned any number. I can provide more info if needed. Excel version is the current 365. I know using xlookups probably solves this. However I'm just more curious about what's going on under the hood to lead to the unexpected behavior of the vlookup. submitted by /u/zygomatic6 [link] [comments]
- Excel formula automatically rewriting itself??Hi all, this is a really odd problem to run into and I am unsure how to proceed from here. I am currently entering data and I have Excel performing an =SUM(B5:AC5) for my data that is being entered. Because the data entry can go pretty fast sometimes, I would keep entering data past AC5 before I realize what happened. When I look at my SUM column, Excel has flagged my =SUM(B5:AC5) formula with the error below, which is fine and whatever, but it is automatically changing my =SUM(B5:AC5) formula to =SUM(B5:AF5) or something of similar depending on what column I stopped at. Just for reference, data from AD5 to AO5 is being summed up in another column with SUM=(AD5:AO5). Formula error being flagged by Excel I have no idea why it is doing this and I tried to ignore error, but it still automatically rewriting my formula anyways! This worksheet and formula worked smoothly 4 months ago so I don't know what is going on. submitted by /u/WildKhanine [link] [comments]
- Links to a cell within current worksheet keep changing the location, or doesn't work at all.I have a series of titles at the top of my worksheet with links to the most popular categories, hopefully to get to those categories quickly for data entry. I right click on the cell where I want the link, select Link, the enter the cell reference I want to jump to, with the text to display. This works. It presents a link, and if i click on the link, it jumps to exactly where I want it to go. However, over time, the cell reference isn't correct, and jumps to something else. I assume this is happening due to modifications of the worksheet or what it's from. I do not use "$" in the cell reference, assuming that if I enter A720 as the cell reference, the reference will automatically be modified to A730 if 10 rows are added. I have enough of these links that I shouldn't have to modify all of them each time there is an insertion or deletion. I tried using the formula =HYPERLINK(A730, "Category1") or =HYPERLINK('Shawna Smith'!A730, "Category1"), and neither of these work at all. It does not jump to A730 when I click on it. What am I doing wrong, or should I be using another feature or formula? Thank you for your help. https://preview.redd.it/nb4q3dm883tg1.png?width=649&format=png&auto=webp&s=cfc0c9e7f2d7e9d620075eebf6def8e7500f09fa submitted by /u/KatMagic1977 [link] [comments]
Tagged with
#Excel alternatives for data analysis#natural language processing for spreadsheets#generative AI for data analysis#Excel compatibility#real-time data collaboration#real-time collaboration#Excel alternatives#rows.com#AI formula generation techniques#modern spreadsheet innovations#machine learning in spreadsheet applications#enterprise-level spreadsheet solutions#digital transformation in spreadsheet software#collaborative spreadsheet tools#AI-driven spreadsheet solutions#cloud-based spreadsheet applications#financial modeling with spreadsheets#automation in spreadsheet workflows#no-code spreadsheet solutions#AI-powered spreadsheet