For example, if LOOKUP_ARRAY is a column, the RETURN_ARRAY should also be a column. This must reflect the shape of your LOOKUP_ARRAY. RETURN_ARRAY: This is the results Excel will show once it finds the LOOKUP_VALUE in your LOOKUP_ARRAY.Otherwise, your formula will return an error. This can be located anywhere in your table but must only be a single row or column. LOOKUP_ARRAY: This is the range of cells Excel will comb through to find the LOOKUP_VALUE you assign.LOOKUP_VALUE: Like the formulas above, this refers to the value that Excel will find.Since the row number for the available inventory is 9, the ROW_INDEX_NUMBER reads 9.įormula: =XLOOKUP(lookup_value,lookup_array,return_array,) ROW_INDEX_NUMBER: This is the number of rows that Excel will count to return a result.Īs shown below, the HLOOKUP formula is used to get the available stock of an SKU.LOOKUP_VALUE: This is the value that Excel will look for in the first row of your table.The HLOOKUP function is almost the same as VLOOKUP, except that your data here should be arranged by row.įormula: =HLOOKUP(lookup_value,table_array,row_index_num,) Since the price is under the 5th column, the value under COL_INDEX_NUMBER is set to 5. In the sample below, the user searched for the price of a specific SKU. If you choose FALSE, it will return exact matches only. If you write TRUE, it will return approximate matches (and your first column should be in ascending order). RANGE_LOOKUP (optional): This can only be either TRUE or FALSE.COL_INDEX_NUMBER: This is the number of columns that Excel will count to return a result.It will then count the number of cells based on the following variable to return a result. Excel will use LOOKUP_VALUE and find it in the first column of the TABLE_ARRAY. TABLE_ARRAY: This is the range of cells where the function will operate.LOOKUP_VALUE: This is the value that Excel will look for in the first column of your table.It will then count across columns based on the number you give and return a corresponding value.įormula: =VLOOKUP(lookup_value,table_array,col_index_num,) Excel looks for the value you assign in the first column of your chosen range. The VLOOKUP function is helpful for data arranged in columns.

Excel multiple if then formula