I’m having the hardest time getting a lookup function to work...
I’ve done it before in the past but for some reason I can’t get it to work today!
Essentially I’d like Table 1 ‘spent’ column to show the total for each day in the table above it (see screenshot). What on earth am I missing?
Oh also please note I used the same arguments for HLOOKUP, before using VLOOKUP.
first things first , those aren't the proper arguments for either vlookup or hlookup.
VLOOKUP(search-for, columns-range, return-column, close-match)
you have a range where the return column number is supposed to go, which would be the number of the column (row for Hlookup).
You can look at the information either from within the program, or in this PDF from Apple:
https://manuals.info.apple.com/MANUALS/0/MA665/en_US/Formulas_and_Functions_User_Guide.pdf
The second issue is how you are organizing your data, the raw data (top table) would be transposed the other way around typically. By adding new columns to the right, two for each day, you are going to limit not only the number of days you can track, but also make it harder to do other common things like filter or sort your data.
I would convert that table into one with Date as a column header, Amount and Reason as the other two. It is fine to enter multiple rows of data with the same date.
Then your vlookup (with a return column update mentioned above) would work fine.
Jason
first things first , those aren't the proper arguments for either vlookup or hlookup.
VLOOKUP(search-for, columns-range, return-column, close-match)
you have a range where the return column number is supposed to go, which would be the number of the column (row for Hlookup).
You can look at the information either from within the program, or in this PDF from Apple:
https://manuals.info.apple.com/MANUALS/0/MA665/en_US/Formulas_and_Functions_User_Guide.pdf
The second issue is how you are organizing your data, the raw data (top table) would be transposed the other way around typically. By adding new columns to the right, two for each day, you are going to limit not only the number of days you can track, but also make it harder to do other common things like filter or sort your data.
I would convert that table into one with Date as a column header, Amount and Reason as the other two. It is fine to enter multiple rows of data with the same date.
Then your vlookup (with a return column update mentioned above) would work fine.
Jason
Return COLUMN!!! That was the issue. Aiyaiyai thank you so much.... I don’t know how it took me so long to realise that! I kept all the arguments the same and just changed the function to LOOKUP, and it looks like it’s working now. Is that what you would’ve done too?
Re: the 2nd issue, you’re right it’s a bit of an odd way to organise it all. I couldn’t think of an effective way to have all the payments I make, for each day, separated and have attached descriptions. If that makes sense?
I initially thought I could just put “=1+2+3, etc” for each payment I made that day, and then another column for each corresponding reason, just as a bullet point (I’ll attach a screenshot so it makes a little more sense...) but then I thought ‘what if I need to modify a payment easily, like if I entered $30 spent on jan 3rd, but actually it was on jan 4th’ I’d then have to go into the formula of the cell and delete that transaction, and also the bullet point description. So I didn’t go through with that.
Oh and! I did also think about having multiple rows of transactions for the same date, like you said, but I thought it might just look too messy? Like same days will have 2 rows, some will have 20, what do you think?
Numbers Lookup
