Can I use COLUMNS() formula instead of COLUMN() in some way.
If you did not think that we did not want to help you, here are two formulas.
This
ARRAY formula below shows all values from Sheet1 from the 'A' column missing in 'A' column on Sheet2. (this
CSE formula, need be finished with Ctrl+Shift+Enter)
Code:
=INDEX(Sheet1!$A$2:$A$51;SMALL(IF(ISNA(MATCH(Sheet1!$A$2:$A$51;$A$2:$A$51;0));ROW($A$2:$A$51)-ROW($A$2)+1);ROWS(H$3:H3)))
This formula all the numbers that exist in the 'A' column on Sheet1, but there are none in the column 'A' on Sheet2 (pay attention that the cell range must be identical, '$A$2:$A$51').
The formula is based only on the first two arguments, in the
INDEX function syntax =INDEX(array;row_num;column_num).
Instead of the "column_num" argument, nested is the SMALL function, this function/formula returns the number of row in which the missing number on Sheet2 appears in the 'A' column.
To make it clearer, use the
Evaluate formula and the 'F9' key at, to view the calculation.
Furthermore, using the
VLOOKUP formula, this formula returns a value from a certain row for a given range if a certain condition is match. (This formula works only with unique values as a condition, so it does not support
duplicate as a condition).
Code:
=VLOOKUP($H2;Sheet1!$A$2:$F$51;COLUMN(B$1);FALSE)
Also, notice that within the
Vlookup formula (instead of the "col_index_num") argument,
nested is COLUMN() function. This function/formula, when copying to across, changes the number of columns from which it returns the result.
This
tutorial of the COLUMN function and
COLUMNS() function is not in English, but the image can help you understand the return of the function result when copying the formula.
And finally, you need to pay attention to the
absolute and relative Excel address if you copying the formula.
If you want to avoid
#NUM! errors, then the
formula neste in IFERROR or IF/ISERROR (Depending on the version of Excel you are using. See note and syntax functions in attacheed file)
To make it easier for you, if you have data on two worksheets (Sheet1 and Sheet2), you first do everything in one worksheet (for example, Sheet1), Then, with the command 'Select/Cut/Paste' all the results you move to Sheet2. Excel will automatically into the formula set the relevant 'Sheet Name Tab' after the paste command.
I hope I helped you learn something new?
For more details and the necessary knowledge, as she said colleague @AliGW, you need to learn the basics of Excel and here
you can find some instructions for beginners in Excel with example formulas.
I hope you asked for this, if you did not, I apologize for my misunderstanding.