INDEX VLookup Multiple Results returing blank
I'm trying to create a Lookup that return multiple results from a table but it continues to return blank even though the data is there.
this is the code:
=IFERROR(INDEX('Simple Entry'!$C$2:$C$1048576,SMALL(IF(Summary!H5='Simple Entry'!$A$2:$A$1048576,ROW('Simple Entry'!$C$2:$C$1048576),""),ROW()2)), "")
any help or other solutions would be much appericatied.
Thanks
See also questions close to this topic

How to Delete COMPLETELY Blank Rows VBA
In my macro, I want to delete completely blank Rows. Not Rows that has something in one column because that is still valuable information.
I have looked all over the internet but if someone could come up with a function that deletes ENTIRELY blank rows and not just rows with something missing in a few columns that would be awesome.
(Information is only on Columns A through N and roughly 7 thousand rows). I haven't developed any code for this because I am really stumped.

Summing csv columns
I have a csv file with a couple columns and about 100k rows. One of the columns is a date, and I was wondering what the easiest way was to count the number of rows that have a certain date for all the possible dates and make a new csv file with just the date and the number of rows that have that date in the specific column. Any language or method is fine!
Thanks

How do I import Subscript and Superscript from an excel file to mySQL Database?
We have a lot of chemistry MCQs in an excel file with lot of chemistry formulas and equations. When we tried to import these MCQs into mySQL based database all subscripts used in formulas lost and taken as normal character e.g, H2SO4, CO2, CH3 etc and makes our formulas and elements name nonsense. Is there any solution by which mySQL can import subscript and superscript as it is from excel? Or Is there any solution so we can force excel to consider subscript and superscript as inserted symbols because mySQL can pick symbols as it is?

Pandas indexing and Key error
Consider the following:
d = {'a': 0.0, 'b': 1.0, 'c': 2.0} e = pd.Series(d, index = ['a', 'b', 'c']) df = pd.DataFrame({ 'A' : 1.,'B' : e,'C' :pd.Timestamp('20130102')}).
When i try to access the first row of column B in the following way:
>>> df.B[0] 0.0
I get the correct result.
However, after reading KeyError: 0 when accessing value in pandas series, I was under the assumption that, since I have specified the index as 'a', 'b' and 'c', the correct way to access the first row of column B (using positional arguments) is:
df.B.iloc[0]
, anddf.B[0]
should raise a Key Error. I dont know what am I missing. Can someone clarify in which case do I get a Key Error ? 
Stop part of a HTML page being indexed for search
I manage an Intranet and we have ads crossselling topics and pages but these are also being indexed.
E.g. we have 1 page on ice cream but an ad which cross sells this page on 150 pages so we have 151 pages indexed for ice cream.
I would like to stop the ad being indexed in search, is there any code/way to do this?
I know the question was already asked but this was 2012 Stop part of page being index by search engines?
Living in hope,
Rodney

Azure Databricks & R: indexing on column using brackets or $sign gives different results
Disclaimer: Because this is on Azure Databricks, I'm not sure how to make a reproducible example for you.
This question is about a script I'm running in Azure Databricks using
R
. I'm working on data which comes in as a plain text file. I separate the rows into columns using theseparate()
and change all column types (e.g. numeric, Date, character etc.). At one point, I need to calculate the years between two dates and I used Calculate ages in RI did this with a couple of other scripts, but with the one I'm using now I ran into this weird situation:
When I index the data.frame using the $ sign, the output is
NULL
, when I index using bracketsmydata[1]
I get the right columnYou can see my code + output below. Does anyone know why this happens or where in my code I should search for the solution? Note: I did this a couple of times before with other data (but same source) and there I did not run into this situation/error.
str(binVer) 'data.frame': 6976 obs. of 39 variables: $ mutdat : Date, format: "20170110" "20170110" ... $ muttijd : chr "11505800" "14050100" "15081000" "14242900" ... $ mutsrt : num 2 2 2 2 2 2 2 2 2 2 ... $ gebdat : Date, format: "19980916" "19930321" ... binVer$mutdat NULL binVer[1] mutdat 1 20170110 2 20170110 3 20170106 4 20170106 binVer$Age< age(binVer$gebdat, binVer$mutdat) Error in as.POSIXlt.default(to) : do not know how to convert 'to' to class “POSIXlt”

VLOOKUP issue in Excel
I have the following basic VLOOKUP setup, having not used Excel in anger for a while. I am looking up the values a, b and C in a table containing two columns.
<value returned> <expected> a 1 b =VLOOKUP(A1,C$1:D$1,1,FALSE) #N/A #N/A b 2 c =VLOOKUP(A2,C$2:D$2,1,FALSE) #N/A 2 c 3 d =VLOOKUP(A3,C$3:D$3,1,FALSE) #N/A 3
I am getting #N/A returned for all rows (as shown to the right), but I would expect the values to the right again to be returned. Can someone please explain what I have done wrong?
Thanks

Calculate Pay using an Age range in excel
I’m trying to put a excel sheet together for a local business We are trying to find out a method to use an excel function to calculate pay based on age using age ranges , Ie if pay age range is 18  21 pay would be $5.00 or if age is 25 and over pay would be $10. Thank you

VBA: cell.Value is not working with a String check
I hope you are doing well!
Quick question here.
I have built a program that sends a report to a specific list of email addresses in Excel. This list pulls from another spreadsheet using a pretty simple VLOOKUP function.
The program works for emails that are not linked in any way to information using the VLOOKUP function. When I hardcode an email address (or even hardcode it in another cell and link it thereafter), my InStr check below works. Otherwise, however, the email address doesn't satisfy the conditions, and an email is not sent to the address.
For context, nameCheck is a string that concatenates all email addresses after they have received an email. The purpose of this check is to prevent multiple sends to the same person (because this list is full of duplicate email addresses). Another part of the program manages the actual content sent to each person.
Neither cell.Text (eMailAddress below) nor cell.Value passes this check for the cells that import information from elsewhere. I even recreated the email address column ("M") to use each person's first and last names (which are provided in another column using VLOOKUP) to no avail. Must the data be hardcoded within this spreadsheet? Somebody will be adding information to this spreadsheet progressively, and I do want each row to contain the appropriate email address.
On that note, is there any way a formulagenerated value could be hardcoded programmatically? Or is there a way to access the actual value and use it to engage the InStr check?
The problem becomes even more unclear when I include a
MsgBox (eMailAddress & " vs. " & cell.Value)
before the If statement. The MsgBox prints both email address values clearly, and they are identical. Neither works for the check, however.The ultimate goal of this program is to send a weekly update, so any recommendations on developing a weekly send trigger (e.g. Monday at 8 a.m.) are most welcome. Ideally, this trigger could occur without having the workbook open.
For Each cell In Columns("M").Cells.SpecialCells(xlCellTypeFormulas) eMailAddress = cell.Text If Cells(cell.Row, "O").Value <= 6 And _ InStr(nameCheck, eMailAddress) = 0 Then Set OutMail = OutApp.CreateItem(0) ' ... Rest of the code below. The rest should work if the email ' addresses can make it past the condition ...
For further context, I use the first range setup because I want to traverse through all nonempty cells in Column M. I originally used xlCellTypeConstants but switched over to Formulas because each value is (kind of) formulacreated using VLOOKUP. Neither works for the relevant cells.
The numerical condition above is not the problem. It checks to make sure the information was added within the last six week days.
Thanks in advance for any kind of help!
I am totally new to VBA, so I am trying to poke my way around!
Cheers!
EDIT: I add a successful email address to nameCheck once the email is sent and before the loop repeats.

In an array, return all values except one
I would like to use a formula to capitalize just the first letter of an array of words. Sometimes the array might have just 1 word, and sometimes 2, 3, 4 or more words. The source is dynamic, so I need my formula to be flexible. I know about
Proper(text)
, but that capitalizes every word.For example, in cell A1 I might have the text "aidan is a good boy,"
or I might just have "hi,"
or maybe it will say, "drive in your own lane please!"My formula over in B1 needs a result of "Aidan is a good boy,"; "Hi,"; or "Drive in your own lane please!"
I wish I could say,
B1: =Proper(index(split(M1, " "), 1)) & " " & lower(index(split(M1, " "), *everything except 1*))
, but I don't know how to fill in the*everything except 1*
part of the formula. 
increase offset formula row by x value in excel
Increasing the length of row by 25 exactly in the formula?
For example
=OFFSET($D$6,(ROW(B1)1)*5,0) =OFFSET($D$6,(ROW(B26)1)*5,0) =OFFSET($D$6,(ROW(B51)1)*5,0)
and so on. When i drag the formula its not considering the rule. 
Compare cell against series of cell pairs
I'm trying to make a LibreOffice spreadsheet formula that populates a column based on another input column, comparing each input with a series of range pairs defined in another sheet and finally outputting a symbol based on matched criteria. I have a series of ranges that specify a

output, and another series that corresponds to+
, but not all inputs will fall into a category. I am using this trinary output later for another expression, which I already have in place.My question becomes: how can I test input against each range pair without spelling out the cell coordinates for each individual cell (ie
OR(AND(">= $A$1", "< $B$1"), AND(">=$A$2", "<$B$2"), ...)
)? Ideally I could just specify an array to compare against like$A$1:$B$4
. Writing it in a python macro would work, too, since I don't plan on sharing this file.I wrote a really quick list comp in python to illustrate what I'm after. This snippet would be one half, such as testing

qualification, and these values may be fed into a condition that outputs the symbol:>>> def cmp(f, r): ... return r[0] <= f < r[1] >>> f = (1, 2, 3) >>> ranges = ((2, 5), (4, 6), (3, 8)) >>> [any([cmp(i, r) for r in ranges]) for i in f] [False, True, True]
Here is a small test example with real input and real ranges.