Finding the "next" result in FINDV

[Juan Magaña]

Hi everyone,

I´m using the FINDV function to find a value in a table. The table contains
dates in the first column, and different values in the rest of the columns.
It works OK, except when a date is not found. In such case, I would like to
obtain the value for the next date available, but instead I get the previous
date (if the 'sorted' parameter is set to TRUE) or #N/A (if the 'sorted'
parameter is set to FALSE).

Is there a way to achieve what I want? I tried to sort the dates in
descending (instead of ascending) but it works even worst....

Thanks, from Spain

 

[Bernard Liengme]

Your FINDV function sounds like VLOOKUP in the US version of Excel

In A1:A10 I have the numbers 1,2,3,4,6,7,8,9,10,11 (notice 5 is missing)
In B1:B11 I have the letters a,b,c,d,e,f,g,h,i,j

In D1 I have the value 4
The formula VLOOKUP(D1, A1:B11, 2) returns the letter d but we want the next
one (e)

The formula =MATCH(D1,A1:A11,1) returns the position of the value 4 in A1:11
So =INDEX(B1:B11,MATCH(D1,A1:A10,1)+1) will return the letter from column B
that is one down from the 4 in column A

Sorry, I have lost my link to the dictionary giving translations of Excel
functions so I cannot tell you the Spanish for MATCH or INDEX

best wishes

 

[Juan Magaña]

Thanks, the Match function did the trick (COINCIDIR in Spanish). It was a
littlel tricky to use it in my particular case but it did work like a charm!!!

P.S. Is it possible to use the MATCH function as the third parameter of the
VLOOKUP function?

Thanks again, from Spain

 

[Bernard Liengme]

Is it possible to use the MATCH function as the third parameter of the

VLOOKUP function?

Yes, this is how ones does a 2-D lookup.
Imagine a table showing how much beef, pork, chicken is raised in a list of
counties
Suppose A2:A20 contains country names, while B2:B40 contains the product
D1 = Spain D2 = beef
VLOOKUP(B2:J20, D2, match(D1,A2:A20)) would locate the right row and column.
(Untested but the idea is correct)

 

[Juan Magaña]

that`s what I tried, but it didn´t work...

Yes, this is how ones does a 2-D lookup.
Imagine a table showing how much beef, pork, chicken is raised in a list of
counties
Suppose A2:A20 contains country names, while B2:B40 contains the product
D1 = Spain D2 = beef
VLOOKUP(B2:J20, D2, match(D1,A2:A20)) would locate the right row and column.
(Untested but the idea is correct)

 

[Myrna Larson]

It works. If you post the formula you used, someone can probably spot your
error.