 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Mac Forum / Applications / Excel / November 2007LookUp returns #N/A for no reason
Hi All - I put together a small sheet to compare some prices. Names of 5 vendors are in B3:F3 & prices for each the 8 products are in those respective columns. In Column H (on each of the price rows) there is a Min fx that looks to the five prices on that row & returns the lowest price for the item on that row - no problem so far. In Column I there is a Lookup fx using the Vector method to look at the Min price in that same row of Column H, find that cell in the prices on the row & return the name of the vendor (Row 3 of that same column containing the lowest price). This works fine for 6 of the 8 products, but 2 of the lookups are returning the #N/A error & I can't figure out why. The other 6 rows were set up the same way & the Lookup fx was copied from cell to cell using abs refs for the result vector. I've checked & reset the formatting, rewritten the formula manually, re-entered the data, tried the error tracing features & everything I can think of with no insights as yet. I've also tried it in 4 different versions of XL (PC & Mac) with the same results. Even the Evaluate feature in PC XL show that the lookup value is fine but indicates that "the next evaluation will result in an error" but won't tell me *why*. The lookup fx is simply: =LOOKUP(H30,B30:F30,$B$3:$F$3) Where am I going wrong? TIA |:>) Bob Jones [MVP] Office:Mac > Hi All - > [quoted text clipped - 27 lines] > Bob Jones > [MVP] Office:Mac Bob, From your description, it would appear that the minimum value can be anywhere within a row. LOOKUP, HLOOKUP, VLOOKUP all require that the entries in lookup_vector are in ascending order. If not, your MIN result from column H may be less than the first (lowest) entry in the lookup_vector and a #N/A would result. Perhaps the MATCH function with a match_type of 0 would be a better choice as the lookup_array can be in any order. Couple the positional result (minus 1) with an OFFSET function from the start of the vendor list $b$3 to get the vendor name. Something like this for the column I: =offset($b$3,0,match(h30,b30:f30,0)-1)) HTH... Art > Hi All - > [quoted text clipped - 23 lines] > > Where am I going wrong? As Art said, Lookup expects a sorted list. Try =INDEX($B$3:$F$3,MATCH(H30,B30:F30,0)) On 11/24/07 8:48 PM, in article jemcgimpsey-2B5D3A.18485224112007@news.microsoft.com, "JE McGimpsey" <jemcgimpsey@mvps.org> wrote: >> Hi All - >> [quoted text clipped - 27 lines] > > =INDEX($B$3:$F$3,MATCH(H30,B30:F30,0)) Hi John & Art - The asc. Sort crossed my mind but since there was no argument for range_lookup (true/false) I thought I'd give it a shot anyway. Although what you say makes sense & I am not questioning you, I think what really threw me was that it works for 6 of the 8 rows - the content of which are as randomly arranged as the content in the cells of the other 2 rows that render the #N/A. Why would those 6 rows work & the other 2 not? Thanks for the responses! Regards |:>) Bob Jones [MVP] Office:Mac > On 11/24/07 8:48 PM, in article > jemcgimpsey-2B5D3A.18485224112007@news.microsoft.com, "JE McGimpsey" [quoted text clipped - 12 lines] > > Thanks for the responses! Bob, The notes on LOOKUP and its' variants indicate: "Important The values must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent." My old Excel4 Function Reference book had some handwritten notes in it that this would not only include the possibility of returning the wrong value from the vector/array, but also an #N/A. Never could figure out the exact algorithm. MATCH is a better choice for the data sets that you have. It does have a couple of "gotchas": a) match_type 0 expects an exact match. I got bitten once by differences in precision between the lookup_value and the lookup_array. b) MATCH interprets the format types of lookup_value and the lookup_array literally (no conversions). Thus, a numeric value which is formatted as text will not match its' numerically formatted equivalent. I got bit by this one also :-(. Art Hi Art - Yeah [like everybody else] I reviewed the help on Lookup & its kin *after* the fact:-) I'm quite familiar with H/V but haven't really had need for the elder version. I may have been assuming that since I was using the vector variant that it would scan the vector the way MIN & MAX do. Even so - having reviewed Help along with the input here - I would not expect it to work properly for any occurrence, let alone the majority - IOW, had I gotten #NAs for every row the problem would have been more obvious. At any rate I've tried both your solution as well as John's & they each provide the desired effect. I find John's to be more "intuitive" [if you'll pardon the term:)] but yours is more "interesting". I need to play around with that one a bit as I haven't used OFFSET before. Thanks again for the replies! Regards |:>) Bob Jones [MVP] Office:Mac On 11/25/07 7:27 AM, in article eTd2j.9403$ht1.1660@trndny01, "Art" <me@nowhere.com> wrote: >> On 11/24/07 8:48 PM, in article >> jemcgimpsey-2B5D3A.18485224112007@news.microsoft.com, "JE McGimpsey" [quoted text clipped - 36 lines] > > Art > Hi Art - > [quoted text clipped - 17 lines] > [MVP] Office:Mac > [...] Bob, Been there many times :-). Unfortunately, the O/L help often trims out useful examples, helpful hints and pointers to other references. That's why I guard my moth-eaten Excel4 reference books with my hen scratchings like gold. wrt to the two alternatives (mine and Johns'), certainly both will work. One subtle difference is that with the INDEX function, the array syntax format #2 (the one that returns a value) requires that the row and column indices both be within the array specification, otherwise a #REF! error will occur. Certainly not an issue when encoding the first time. However, if you expand your tables to more vendor columns and absolute references had been specified, the array specification may need to be modified for every row. Using the OFFSET function, the formulae can remain unchanged as only the first cell in your vendor name list needs to be specified. As I mentioned, the OFFSET function does require an adjustment of -1 from the MATCH result. The INDEX approach doesn't. Not sure why Excel didn't use a consistent approach to indexing and offsets, but that horse left the barn almost 20 years ago :-). I got bite marks from these "differences" more than once :-). Art > One subtle difference is that with the INDEX function, the array syntax > format #2 (the one that returns a value) requires that the row and [quoted text clipped - 9 lines] > As I mentioned, the OFFSET function does require an adjustment of -1 > from the MATCH result. The INDEX approach doesn't. Another difference is that OFFSET is volatile while INDEX is not. For most applications that does not make a lot of difference, but if you have a lot of these functions, INDEX will be more efficient. A side effect of OFFSET()'s volatility is that simply opening a file which uses OFFSET will mark the workbook as "dirty", leading to a "do you want to save" message when you close the workbook, even if you did nothing to change any values or calculation results. >> One subtle difference is that with the INDEX function, the array syntax >> format #2 (the one that returns a value) requires that the row and [quoted text clipped - 19 lines] > you want to save" message when you close the workbook, even if you did > nothing to change any values or calculation results. John, Interesting. I think you just answered an age-old question in an old XLM4 macro program that I have. Any others come to mind that mark the sheet as modified ? Art > Interesting. I think you just answered an age-old question in an old > XLM4 macro program that I have. Any others come to mind that mark the > sheet as modified ? From the MS KnowledgeBase: http://support.microsoft.com/kb/274500/en-us CELL() OFFSET() TODAY() INDIRECT() NOW() INFO() RAND() However, this article, applicable to WinXL2000: http://support.microsoft.com/kb/248179/en-us lists these functions: AREAS() INDEX() OFFSET() CELL() INDIRECT() ROWS() COLUMNS() NOW() TODAY() RAND() I know that INDEX() *used* to be volatile in pre-MacXL04 and -WinXL02 versions, but is no longer so. By test, neither are COLUMNS(), ROWS() and AREAS() in XL04. >> Interesting. I think you just answered an age-old question in an old >> XLM4 macro program that I have. Any others come to mind that mark the >> sheet as modified ? > > From the MS KnowledgeBase: >[...] Thanks, John. Some like INFO(), NOW(), DATE(), RAND() are obvious. Some of the others are a bit surprising, but good to know about in any event. Art |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.