Finding the "next" result in FINDV

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
Is it possible to use the MATCH function as the third parameter of the
VLOOKUP function?
Click to expand...


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)
 
that`s what I tried, but it didn´t work...

Bernard Liengme said:
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)
Click to expand...
 
Back
Top