MS Excel

 MS Excel 

Loop Function

Q1.what is loop function?

Ans

 A loop is a programming construct that repeatedly executes a statement or condition within predetermined bounds. Advertisements. In all programming languages, the loop function employs logic and syntax that are essentially equivalent.

Lookup function always work column/row  wise and compare value

by each cell

Most commonly used lookup function :

    1. Lookup()

    2. Hlookup()

    3. Match()

    4. Index()

1. Lookup():-

   When you need to search a single row or column and find a value from the same point in a different row or column, use the lookup and reference function LOOKUP.

syntax:

   VLOOKUP(lookup value, table array, col_index_num,[range_lookup])

i. lookup value: value search in first column

ii. table array: complete data selection

iii. Col index num : number of column , which value is fetch 

iv. range lookup: match/related

example:-

2. Hlookup():-

   When you wish to look down a specific number of rows and your comparison values are arranged in a row across the top of a data table, use H Lookup. When your comparison values are in a column to the left of the data you're looking for, use VLOOKUP. H LOOKUP stands for "Horizontal," not "Horizontal."

syntax:

 h lookup(lookup _value, table _array, row _index_num_[range _lookup])

lookup value: value search in first row

table array: complete data selection

row index num: number of row which value is fetch

range lookup: match/ related

example:

3. Match():-   

    The MATCH function looks for a specific item within a set of cells, finds it, and then returns the item's position within the set of cells. For instance, the formula =MATCH(25,A1:A3,0) yields the number 2 because 25 is the second item in the range if the range A1:A3 contains the numbers 5, 25, and 38.

syntax:

= MATCH(lookup _value ,lookup _array ,match _type)

example:

=match(searching value ,selection of column ,exact match)

                                or

                    match(I3,B2:B11,0)

4. index():-

   The INDEX function extracts a value from a table or range, or a reference to a value, and returns it. he INDEX function can be applied in one of two ways: See Array form if you wish to return the value of a specific cell or array of cells. See Reference form if you want to give a reference to a set of cells.

syntax:

=Index(array ,row _ num ,col _ num)

note: Do not include heading area in selection 

example:

=index(selection of table,2,5)

                    or

    =index(A2:E11,5,2)

INDEX WITH MATCH

Q 1. what is index match in excel?

The INDEX MATCH[1] Formula combines the INDEX[2] and MATCH[3] Excel functions. Based on the column and row numbers, the function =INDEX() retrieves the value of a cell in a table. The position of a cell in a row or column is returned by =MATCH().

syntax:

=index(A2:E11,match(H9,A2:A11,0),2)

Selection of table 

Searching  value cell 

Selection of column

=INDEX(A2:E11,MATCH(H12,A2:A11,0),MATCH(G13,A1:E1,0))

Heading range 

EMP _ID 

Selection of table 

List of column_ heading 

Column of EMP_ID

Q 2.what is vlookup and match in excel?

The VLOOKUP function can be written as follows in its most basic form: =VLOOKUP(What you want to look up, where you want to look it up, column number in the range holding the value to return, return an approximate or exact match - specified as 1/TRUE, or 0/FALSE).

syntax:

=VLOOKUP(EMP_ID,TABLE ARRAY,MATCH(COLUMN NAME,SELECTION OF HEADINGS,0),FALSE)

=Vlookup(D13 ,A1: E11 match (C14 ,A1:E1,0),false)