Apply CHOOSE Function with Array to Return Multiple Values in Excel
Let’s say, we have a dataset (B4:D12) in Excel containing the Names of some students and their marks in Math and Physics. However, we need to pick a student’s Name along the marks in the two subjects. Here, we will use the CHOOSE function in Excel with the array to return these multiple values. By applying the formula of this method, we will always get the values in a row even if they are in a column. The steps are below.
Using CHOOSE Function along with VLOOKUP in Excel
In Excel, managing space is a very important point to consider. Now, let’s suppose you are in a situation where you want to compare multiple columns in the Excel dataset, and you want to derive results, This is not possible using the VLOOKUP function alone. So, to make a function capable of doing something like this, we will introduce a special function known as CHOOSE function. But, if you don’t want to use CHOOSE function, then you have to use a helper column, making the job a little hectic.
So, in this article, we will see how to use VLOOKUP along with CHOOSE function.
First, we will see the syntax of CHOOSE FUNCTION in brief.
CHOOSE FUNCTION:
Syntax:
CHOOSE(ARRAY DIMENSIONS(INDEX VALUE(S)),VALUE(1),VALUE(2),VALUE(3),.....)
Excel Keyboard Shortcuts جميع اختصارات لوحة المفاتيح للاكسل
Let’s say you would like to filter the Region Column in your data by the value the user inputs in a cell
Assuming that you know how to load the Data (both tables) in Power Query, let’s see what
خطة الفحص السنوية لمصلحة الضرائب المصرية حتى 30-6-2024
 |
Excel OFFSET Function for Dynamic Calculations
Excel OFFSET Function for Dynamic Calculations
Do you want to create formulas that “move” with your raw data set?
That’s when you need to use Excel’s OFFSET function.
OFFSET is a great formula whenever you have dynamic ranges involved. Some examples are:
Calculating the average of the last three months when you add new data to your table
Getting data from the last cell in your raw data range – either last row or last column in that range
Grabbing data from a data table based on a point of view chosen by the user
Complex calculations that require “moving” data sets
3 Practical cases of Excel OFFSET are shown in this Video:
فحص ضريبة المرتبات ومعالجة بنودها ضريبيا
نقلا عن الاستاذ / مجدي ياسين
