Recent Posts1351
Post New Requests Here / Re: In huge need of a formula/macro in MS Excel
« Last post by Target on August 20, 2009, 10:35 PM »I can't access the original files, so I don't know what your data looks like, but
I can't access the original files, but a lookup would have been better in the example than using index/match formulas together with conditional formatting
index/match is the lookup you use when the value you are returning is to the left of your lookup value (ie if you want to lookup a value in column C, but return a value from column A)
and so long as the lookup values don't have multiple results (eg where looking up MOE could return 1, 2, or 3) vlookup is perfectly acceptable (sorted or otherwise, at least that's my experience, just use the exact match option)
error values in your results can also be avoided by using conditional statements, eg
=if(iserror(vlookup(value, range, column, TRUE)),"",vlookup(value, range, column, TRUE))
if the lookup value isn't found it returns a blank, otherwise it returns the lookup value
that could be extended so that blank cells don't calculate, eg
=if(cell="","",if(iserror(vlookup(value, range, column, TRUE)),"",vlookup(value, range, column, TRUE)))
if your reference range is only small, use absolute referencing, or a named range
Here's one way using a formula with the Match and Index functions (Vlookup is shorter but requires the lookup list to be sorted)
Rates.xls is a spreadsheet that has haircut rates (column C) for the 3 Stooges (column A). This would correspond to your "Archive" worksheet.
Formula.xls is a spreadsheet that will populate column C if you type a customer name in column A (only for rows 2 through 15, since the formulas in column C are only in those rows).
If there is no name in Column A, or the name in Column A is not one of the 3 Stooges, then the conditional formatting of C2:C15 hides the #N/A.
You could also populate column C with an Event Change macro, much more elegant.-AndyM (August 20, 2009, 10:03 PM)
I can't access the original files, but a lookup would have been better in the example than using index/match formulas together with conditional formatting
index/match is the lookup you use when the value you are returning is to the left of your lookup value (ie if you want to lookup a value in column C, but return a value from column A)
and so long as the lookup values don't have multiple results (eg where looking up MOE could return 1, 2, or 3) vlookup is perfectly acceptable (sorted or otherwise, at least that's my experience, just use the exact match option)
error values in your results can also be avoided by using conditional statements, eg
=if(iserror(vlookup(value, range, column, TRUE)),"",vlookup(value, range, column, TRUE))
if the lookup value isn't found it returns a blank, otherwise it returns the lookup value
that could be extended so that blank cells don't calculate, eg
=if(cell="","",if(iserror(vlookup(value, range, column, TRUE)),"",vlookup(value, range, column, TRUE)))
if your reference range is only small, use absolute referencing, or a named range
), then ran with BBLean for a while but stopped after a rebuild - as others have noted there weren't enough benefits for me to bother with it
) and would prefer a 'headless' system, which I suppose implies some sort of server OS (again, something I'm not familiar with), and controlled via a remote or gamepad 
(at least that is the only name from type of dog that is known to me)