Google Sheets LOOKUP unable to find lookup value even if there is

Question

=lookup(A4,'LTO Renewal Record'!B2:B20,'LTO Renewal Record'!C2:C20)

Above code is suppose to return a date value. However, it gives me an error saying "Did not find value "my value" in LOOKUP evaluation." even though it is there.

Here's a link of the Google Sheet I'm trying to work on.

What I would like to do here is to lookup the latest value it can find on the column, and add 1 year to it since I am trying to track the expiration date of the vehicle's registration.

This is my code in MS Excel VBA when adding one year to the lookup value:

.Range("R" & MatchRow + 13).Value = DateAdd("yyyy", 1, TextBox2.Value)

However, I haven't the faintest idea how to have this working on Google Sheet, I've tried researching online but unable to find what I'm looking for. Hope someone can help.

score 2 · Answer 1 · answered Jul 04 '19 at 08:13

2

LOOKUP only works if the data is sorted. See documentation here: https://support.google.com/docs/answer/3256570?hl=en

In this case, I would suggest using something like VLOOKUP. Try this: =vlookup(A4,'LTO Renewal Record'!$B$2:$C$20,2,). (I added the $ to lock the references, in case you need to copy/paste it around.) You can see the documentation for VLOOKUP here: https://support.google.com/docs/answer/3093318?hl=en

answered Jul 04 '19 at 08:13

James

153
9

I did try VLOOKUP first, however, it gets the first match it can retrieve. I have multiple results for my lookup value, and as per my question, I would like to get the latest added data. – Kelvs Jul 04 '19 at 08:35
Ah, sorry for the misread. I missed that detail. Would it be possible to add helper columns to your data table `LTO Renewal Record`? I think if you don't mind doing that, you can do a `COUNTIF` and do a multiple-criteria `VLOOKUP` (or `INDEX`/`MATCH` to search for the highest-valued `COUNTIF`). – James Jul 04 '19 at 08:43
I made an example of how such a solution would be laid out for your data set here: https://docs.google.com/spreadsheets/d/12ZoHvflFAH6V_XUoO7sJYdVe2yINV8uWckW9HkOzBE4 – James Jul 04 '19 at 08:56

player0 · Accepted Answer · 2019-07-04T09:42:03.263

=TEXT(DATE(YEAR(VLOOKUP(A4, SORT('LTO Renewal Record'!B2:C20, 2, 0), 2, 0))+1, 
          MONTH(VLOOKUP(A4, SORT('LTO Renewal Record'!B2:C20, 2, 0), 2, 0)), 
            DAY(VLOOKUP(A4, SORT('LTO Renewal Record'!B2:C20, 2, 0), 2, 0))), 
 "mmm dd, yyyy")

=ARRAYFORMULA(IFERROR(TEXT(
 DATE(YEAR(VLOOKUP(A4:A, SORT('LTO Renewal Record'!B2:C20, 2, 0), 2, 0))+1, 
     MONTH(VLOOKUP(A4:A, SORT('LTO Renewal Record'!B2:C20, 2, 0), 2, 0)), 
       DAY(VLOOKUP(A4:A, SORT('LTO Renewal Record'!B2:C20, 2, 0), 2, 0))), "mmm dd, yyyy")))

Google Sheets LOOKUP unable to find lookup value even if there is

2 Answers2