Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
=IF(ISNA(VLOOKUP(A1,Data!$B$1:$C$200,MATCH(A1,Data !$A$1:$C$1,IF($V
Someone sent me a file with this formula and I would like to know how it is
working and what it is trying to do: =IF(ISNA(VLOOKUP(A1,Data!$B$1:$C$200,MATCH(A1,Data !$A$1:$C$1,IF($V1 = 2,-1,1)),)),0,VLOOKUP(B1,Data!$B$1:$C$200,MATCH(A1,Da ta!$A$1:$C$1),)) Thank you in advance. Nancy |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
=IF(ISNA(VLOOKUP(A1,Data!$B$1:$C$200,MATCH(A1,Data !$A$1:$C$1,IF($V
Working from inside - out...
The first MATCH arguement is checking to see if A1 is located on Data sheet in cells A1:C1. If V1 = 2, then it will search in descending order, else it will search in ascending order. This MATCH function then returns an integer that controls what column to look at in the VLOOKUP function. If A1 is found in the VLOOKUP table, 0 is returned. (The reason for the ISNA() arguement, and the overall IF function.) If it is not found, the formula looks for B1 in the VLOOKUP table, with the column to be check determined by a MATCH function, which looks in ascending order. Without looking at the sheet, it appears someone is trying to determine which column of a data table to look at. However, if A1 is found within that table, return 0. Else, find the correlating value. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "nv77078" wrote: Someone sent me a file with this formula and I would like to know how it is working and what it is trying to do: =IF(ISNA(VLOOKUP(A1,Data!$B$1:$C$200,MATCH(A1,Data !$A$1:$C$1,IF($V1 = 2,-1,1)),)),0,VLOOKUP(B1,Data!$B$1:$C$200,MATCH(A1,Da ta!$A$1:$C$1),)) Thank you in advance. Nancy |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
=IF(ISNA(VLOOKUP(A1,Data!$B$1:$C$200,MATCH(A1,Data !$A$1:$C$1,IF($V
=IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)
This tests to see if the result cannot be found (is N/A). If the first VLOOKUP results in nothing (or N/A), then the test fails and "0" is returned as your answer. Otherwise, the second VLOOKUP is run and the result is returned. VLOOKUP(A1,Data!$B$1:$C$200,MATCH(A1,Data!$A$1:$C$ 1,IF($V1=2,-1,1)),)) This is the first test and should be broken down a bit to understand it. First, it is searching for the content of cell A1 in another worksheet (that worksheet's name is "Data") in column B, rows 1 through 200. Let's say cell A1 has the word "Apple"...this searches for that word in Data!B1:B200. Because it searches for one thing in a vertical column 200 rows tall, it is called V (or Vertical) Lookup. Once it finds the first instance of whatever is in cell A1, the VLOOKUP will then return a value to the right of that value. So if it found "Apples" in Data!B25, then it might return the value from Data!C25. Since the VLOOKUP was limited to columns B & C (it said B1:C200), you cannot return the value from, say, D25 or E25. At the end of this particular VLOOKUP you'll notice a comma with nothing after it, they just close that part of the formula out with closing parentheses. That means the VLOOKUP is not necessarily looking for an exact match. It will find the closest match it can in a SORTED list without going over the desired result. So Data!B1:B200 must be sorted and it will find the first instance that does not exceed your wish. So if it couldn't find "Apples" but did find "Apple" in B25 and "Applesauce" in B26, it would stop testing at B26 and say that B25 was the best match without going "over" (because it assumes everything after B26 all start with higher letters, like Bananas, Berries, Coconut, and so on in an alphabetized list). You would assume that since the VLOOKUP was so limited to just columns B & C, the user would just return the value from C (which you would just indicate by typing ",1" to indicate C is 1 row to the right of column B). But they're adding one more instruction which is not clear to me. They are trying to match the data in cell A1 to the value in either Data!A1, Data!B1, or Data!C1. The trick is that one of those 3 cells probably does not equal the value of cell A1, so then they say if cell V1= the number 2, then it wants the value from Data!A1:C1 that is the smallest value greater than or equal to the data in cell A1. Otherwise, if V1 is not equal to the number 2, it wants the largest value in Data!A1:C1 that is less than or equal to the data in cell A1. Without more info, I cannot tell what the point of this test is. Either the VLOOKUP returns the value from column B (using ,0) or column C (using ,1), so this test is a little unusual. I'm assuming Data!A1, Data!B1, and Data!C1 must have either 0 or 1 in each cell (so we know if they want the value from B25 or C25), but I'm befuddled. So now we've determined whether the first VLOOKUP returns a valid result or not (honestly, the result itself does not matter...all we wanted to know is that it does not come up with nothing, or N/A). No result returns "0". Otherwise, we run the second VLOOKUP to give us the answer we're really after. VLOOKUP(B1,Data!$B$1:$C$200,MATCH(A1,Data!$A$1:$C$ 1),) So then we have the actual result we're probably hoping for in the first place via a second (different) VLOOKUP. It tests to see if the data in cell B1 (not A1) can be found in the sorted list of Data!B1:B200 (so just like before, but instead of searching for "Apples", perhaps B1 says "Fuji" and it's now searching for that in the same list it was searching the first time around. Again, if it finds the value of B1 in the list Data!B1:B200 without going over, then we're limited to returning either the value from Data's column B or column C. This time they've said find the largest value in Data!A1:C1 less than or equal to the value of cell A1...if that match returns a 0, we'll pull the value from column B; if that match returns a 1, we'll pull the value from column C; if that match returns anything else, you'll get an error. So again, I'm stumped on the whole point of all the "MATCH" tests but with more info we can probably help. -- Please remember to indicate when the post is answered so others can benefit from it later. "nv77078" wrote: Someone sent me a file with this formula and I would like to know how it is working and what it is trying to do: =IF(ISNA(VLOOKUP(A1,Data!$B$1:$C$200,MATCH(A1,Data !$A$1:$C$1,IF($V1 = 2,-1,1)),)),0,VLOOKUP(B1,Data!$B$1:$C$200,MATCH(A1,Da ta!$A$1:$C$1),)) Thank you in advance. Nancy |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
=IF(ISNA(VLOOKUP(A1,Data!$B$1:$C$200,MATCH(A1,Data !$A$1:$C$1,I
Oops, I switched the arguements. I meant to say if A1 *isn't* found in the
VLOOKUP table, it returns a 0, else it checks for B1. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: Working from inside - out... The first MATCH arguement is checking to see if A1 is located on Data sheet in cells A1:C1. If V1 = 2, then it will search in descending order, else it will search in ascending order. This MATCH function then returns an integer that controls what column to look at in the VLOOKUP function. If A1 is found in the VLOOKUP table, 0 is returned. (The reason for the ISNA() arguement, and the overall IF function.) If it is not found, the formula looks for B1 in the VLOOKUP table, with the column to be check determined by a MATCH function, which looks in ascending order. Without looking at the sheet, it appears someone is trying to determine which column of a data table to look at. However, if A1 is found within that table, return 0. Else, find the correlating value. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "nv77078" wrote: Someone sent me a file with this formula and I would like to know how it is working and what it is trying to do: =IF(ISNA(VLOOKUP(A1,Data!$B$1:$C$200,MATCH(A1,Data !$A$1:$C$1,IF($V1 = 2,-1,1)),)),0,VLOOKUP(B1,Data!$B$1:$C$200,MATCH(A1,Da ta!$A$1:$C$1),)) Thank you in advance. Nancy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If isna match vlookup formula | Excel Worksheet Functions | |||
VLOOKUP and LEN/ISNA to match names? | Excel Worksheet Functions | |||
ISNA VLOOKUP any data prefixed with a "C7" cannot be found? | Excel Discussion (Misc queries) | |||
Leave cell data (IF(ISNA(VLOOKUP) is TRUE instead of returning blank (" ") or #NA | Excel Worksheet Functions | |||
match data to reference then vlookup | Excel Discussion (Misc queries) |