Sophia has attended:
Excel Advanced course
Vlookup for comparing values
Hi
vlookup query usually used at work and since the course haven't really grasped how to apply the examples used...
Qery below is used to compare values across 2 sheets. where the 'Zema sql output' sheet has data dump.
is there anyway to get an explanation on it if possible?
=IF(ISERROR(VLOOKUP($A$1&$A28857&$B28857,'ZEMA SQL Output'!$A$2:$J$18227,3,FALSE)),"",VLOOKUP($A$1&$A28857&$B28857,'ZEMA SQL Output'!$A$2:$J$18227,3,FALSE))
hope this makes sense!
Thanks
Sophia
RE: Vlookup for comparing values
Hi Sophia, thanks for your query. You've got a Lookup value in there which appears to be the result of a concatenated set of cell values. For example, in the first lookup:
$A$1&$A28857&$B28857
Separating this out into individual references:
$A$1
&$A28857
&$B28857
I obviously don't know what is in those cells but if the end result is a text string try concatenating open and close quotations marks, as the lookup will be expecting them. Also, the formula appear to be testing the value in the third column of the data dump and to only return the value if it doesn't contain an error. Is that correct?
Hope this helps,
Anthony
RE: Vlookup for comparing values
Thanks Anthony,
I now understand the concatenated cells which returns dates and corresponding values if it finds it in the data dump... (sorry I might not be painting the full picture, let me try to)
there are 5 columns with the vlookups checking, comparing and returning matching values from the data dump sheet using the concatenated cells values.
Yes your correct but it returns the exact value (if there) after comparing with cells in the 1st part of the formula.
So the first part for the formula =IF(ISERROR - why does the ISERROR precede the vlookup (why there at all)?
Is it possible to know if this is the most efficient formular of testing the values?
Hope still makes sense!!
Thanks again
Sophia
RE: Vlookup for comparing values
Hi Sophia. ISERROR is there to test whether the first VLOOKUP finds a cell with VALUE! or REF! in it. If it does find either of those it will return a TRUE value and your output will be the value if true in your IF statement (in this case, ""). If the VLOOKUP finds something, ISERROR will return false and the IF statement will perform the value if false lookup expression. ISERROR has been included in this formula because you have cells in your source data which either don't contain anything or have error keywords in them. You could simplify the formula by removing any such errors in the data but this might not be viable. As it stands, your formula structure is used widely to perform conditional lookups so without looking at the source data I think you may be stuck with it.
The best way to understand such formulas is to build them yourself. As an exercise, simply ask yourself how you would go about getting the results you need through a formula. If you start to build it you'll almost certainly end up with a similar formula, only it will make a lot more sense!
Hope this helps,
Anthony
