index in excel
052 index - match - vlookup - hlookup CHOOSE in excel
2 تحميل كتاب الإكسل للمهندسين pdf
3 كتاب مجاني شرح أكثر من 100 دالة من الإكسل
4 كتاب متقدم جدا 👌 كتاب طرق الاهلاك باستخدام دوال الاكسيل
The INDEX function in Excel returns the value at a given location in a range or array. The syntax of the INDEX function is:
=INDEX(array, row_num, [column_num], [area_num])
array is the range or array of cells that contains the value you want to return.
row_num is the row number of the value you want to return. If you omit this argument, INDEX will return the value in the first row of the array.
column_num is the column number of the value you want to return. If you omit this argument, INDEX will return the value in the first column of the array.
area_num is the area number of the value you want to return. This argument is optional and only used when the array is a multi-dimensional array.
For example, the following formula will return the value in the cell A2 of the range A1:B5:
=INDEX(A1:B5, 2, 1)
You can also use the INDEX function to return a reference to a cell or range of cells. For example, the following formula will return a reference to the range A1:B5:
=INDEX(A1:B5, 1, 1)
The INDEX function is a very versatile function and can be used to perform a variety of tasks in Excel. For more information, please refer to the Microsoft Excel documentation: https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd.
Here are some examples of how to use the INDEX function in Excel:
To return the value in the cell A2 of the range A1:B5, use the formula:
=INDEX(A1:B5, 2, 1)
To return the value in the third row of the range A1:B5, use the formula:
=INDEX(A1:B5, 3, 1)
To return the value in the second column of the range A1:B5, use the formula:
=INDEX(A1:B5, 1, 2)
To return the value in the cell A2 of the range A1:B5, but only if the value in cell A1 is greater than 100, use the formula:
=INDEX(A1:B5, 2, 1, IF(A1>100, 1, 0))
=============================
match in excel
1
The MATCH function in Excel searches for a specified value in a range of cells and returns the relative position of that value. The syntax of the MATCH function is:
=MATCH(lookup_value, lookup_array, match_type)
lookup_value is the value you are searching for.
lookup_array is the range of cells where you are searching for the value.
match_type specifies how the search is performed. The possible values for match_type are:
0: Exact match.
1: Match the first occurrence of the value, regardless of case.
-1: Match the largest value that is less than or equal to the lookup_value.
-2: Match the smallest value that is greater than or equal to the lookup_value.
For example, if the range A1:A5 contains the values 1, 2, 3, 4, and 5, and you want to find the position of the value 3, you would use the following formula:
=MATCH(3, A1:A5, 0)
This formula would return the number 3, because 3 is the third value in the range A1:A5.
You can also use the MATCH function to find the position of a partial match. For example, if the range A1:A5 contains the values "John", "Jane", "Mary", "Peter", and "Susan", and you want to find the position of the first name that starts with the letter "J", you would use the following formula:
=MATCH("J*", A1:A5, 1)
This formula would return the number 1, because "John" is the first name in the range A1:A5 that starts with the letter "J".
The MATCH function is a powerful tool that can be used to find the position of a value in a range of cells. It can be used for a variety of tasks, such as finding the value of a cell that matches a certain criteria, or finding the row or column number of a cell that contains a certain value.
====================
vlookup an hlookup in excel
The VLOOKUP and HLOOKUP functions in Excel are both used to look up values in a table. The main difference between the two functions is that VLOOKUP looks up values in a table that is arranged vertically, while HLOOKUP looks up values in a table that is arranged horizontally.
The syntax of the VLOOKUP function is:
=VLOOKUP(lookup_value, lookup_array, column_index_number, [range_lookup])
lookup_value is the value you are looking for.
lookup_array is the range of cells where you are looking for the value.
column_index_number is the number of the column that contains the value you want to return. The column numbers start at 1.
range_lookup specifies whether you want an exact match or an approximate match. The possible values for range_lookup are:
0: Exact match.
1: Approximate match.
For example, if the range A1:B10 contains the values 1, 2, 3, 4, and 5 in the first column and the values "John", "Jane", "Mary", "Peter", and "Susan" in the second column, and you want to find the name that corresponds to the value 3, you would use the following formula:
=VLOOKUP(3, A1:B10, 2, 0)
This formula would return the value "Mary", because Mary is the third name in the range A1:B10.
The syntax of the HLOOKUP function is:
=HLOOKUP(lookup_value, lookup_array, row_index_number, [range_lookup])
lookup_value is the value you are looking for.
lookup_array is the range of cells where you are looking for the value.
row_index_number is the number of the row that contains the value you want to return. The row numbers start at 1.
range_lookup specifies whether you want an exact match or an approximate match. The possible values for range_lookup are:
0: Exact match.
1: Approximate match.
For example, if the range A1:B10 contains the values 1, 2, 3, 4, and 5 in the first column and the values "John", "Jane", "Mary", "Peter", and "Susan" in the second column, and you want to find the value that corresponds to the name "Mary", you would use the following formula:
=HLOOKUP("Mary", A1:B10, 2, 0)
This formula would return the value 3, because Mary is in the second row of the range A1:B10.
The VLOOKUP and HLOOKUP functions are both very powerful tools that can be used to look up values in a table. The best function to use will depend on the way your data is arranged.
==================
CHOOSE in excel
The CHOOSE function in Excel is a versatile function that can be used to return a value from a list based on a number. The syntax of the CHOOSE function is:
=CHOOSE(index_num, value1, [value2], ...)
index_num is the number that specifies which value to return. The index number can be a number between 1 and 254, or it can be a cell reference that contains a number between 1 and 254.
value1 is the first value in the list.
value2 is the second value in the list.
... is an optional list of additional values.
For example, the following formula will return the value "red" if the cell A1 contains the number 1, the value "blue" if the cell A1 contains the number 2, and the value "green" if the cell A1 contains any other number:
=CHOOSE(A1, "red", "blue", "green")
You can also use the CHOOSE function to return a reference to a cell or range of cells. For example, the following formula will return a reference to the range A1:B1 if the cell A1 contains the number 1, the range C1:D1 if the cell A1 contains the number 2, and the range E1:F1 if the cell A1 contains any other number:
=CHOOSE(A1, A1:B1, C1:D1, E1:F1)
The CHOOSE function is a powerful tool that can be used to return a value from a list based on a number. It can be used for a variety of tasks, such as creating a drop-down list, generating random numbers, and creating a table of contents.
Here are some other examples of how to use the CHOOSE function in Excel:
To return the value in the cell A2 if the cell A1 is greater than 100, and the value in the cell A3 if the cell A1 is less than or equal to 100, use the formula:
=CHOOSE(IF(A1>100, 2, 3), A2, A3)
To return the first name in the range A1:A10 that starts with the letter "J", use the formula:
=CHOOSE(MATCH("J*", A1:A10, 1), A1, A2, A3, ...)
To return the sum of the values in the range A1:A10 if the cell A1 contains the number 1, the average of the values in the range A1:A10 if the cell A1 contains the number 2, and the maximum value in the range A1:A10 if the cell A1 contains any other number, use the formula:
=CHOOSE(A1, SUM(A1:A10), AVERAGE(A1:A10), MAX(A1:A10))