May 17 2023 01:48 PM
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 to display the value of the matching data point. I need the program to do this for the entire set B. What function(s) would I use to accomplish this?
May 17 2023 02:27 PM - edited May 17 2023 02:28 PM
SolutionIF 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)
May 18 2023 05:43 AM
@mtarler Thank you! In your formula, is set B and set A just the amount of cells in each set? Also, I would really like to know how these functions work, can you explain why the formula looks like that?
May 18 2023 06:45 AM
May 18 2023 08:40 AM
May 18 2023 08:49 AM
May 17 2023 02:27 PM - edited May 17 2023 02:28 PM
SolutionIF 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)