Forum Discussion
Solved
Comparing lists and returning values
Hello, I am working on a project where I need to compare two data set from different workbooks. I need to compare data point x of set B with the entirety of set A. If a match is found, I need it ...
IF you have Excel 365 you can do the whole set using something like:
=XLOOKUP( [list of values in B], [range to lookup in A], [range to return from A], "match not found")
if you don't have Excel 365 then something like =IFERROR(VLOOKUP(setB!A1, setA!$A:$B, 2), "match not found") and then fill down (unless it is in a table in which case you would use [@value] and it would automatically fill down)
but if the lookup column is after the return value column then you will need to use INDEX(MATCH()) combination instead of VLOOKUP (or HLOOKUP)
mtarler This helps! Thank you so much!
I have Excel 2016, so I will have to use the second formula. With the second formula, the VLOOKUP function is basically saying (look for x, inside of y, if you find it return that same value)?
I have Excel 2016, so I will have to use the second formula. With the second formula, the VLOOKUP function is basically saying (look for x, inside of y, if you find it return that same value)?
Vlookup format is
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
which is look for "lookup_value" in the first column of this "table_array" and then return value in column "col_index_num"
the [range_lookup] is optional but should be included as 0 (zero) to say you want an exact match, otherwise the list must be in order and then you get the closest value.
more info I recommend many google sites but here is Microsoft's official:
https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188#bm12
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
which is look for "lookup_value" in the first column of this "table_array" and then return value in column "col_index_num"
the [range_lookup] is optional but should be included as 0 (zero) to say you want an exact match, otherwise the list must be in order and then you get the closest value.
more info I recommend many google sites but here is Microsoft's official:
https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188#bm12