How to use VLookUp Function?

---

Query Source    : Excel Macros Google Group
Solution Type   : Functions and Formulas
Query by               : Gargi, FICO-SAP
Solution by         : Ashish Jain (MCAS; MCA; Lead Trainer, Success Electrons)

---

Query / Problem:
Dear All,
Can any of you please help me with how to work on V Look Up , and use functions such as transpose and concatenation. I am working In SAP as FICO .It would be of great help if any of you can give other  relevant functions required for SAP datas to be taken up in Excel.
Thanks in Advance
Regards,
Gargi

---

Solution:

The VLOOKUP function returns a value of a cell from one of the table columns. The VLOOKUP function is an extension of the LOOKUP function.
The letter V at the beginning of the function is an abbreviation for vertical. VLOOKUP searches for a value in the leftmost column of a table and then returns a value in the same row from a column you specify in the table.

Figure 1: Shows the results and use of Vlookup function.

i) Searching a value

ii) Searching and Returning a value

Figure 2: Shows the Arguments Box for Vlookup function.

Description of Arguments:

1.Lookup_value: The value for which the calculation is made or the value to be searched in first column of Table_array.
2. Table_array: The range of data where lookup_value is to be searched and the range containing the result.
3. Col_Index_num: Column number of result column. This can be from 1 to total number of columns in Table_array.
4. Range_lookup: This can be True(1) or False(0) only as you need exact match or approximate match respectively.

---

CEILING and FLOOR Function

---

Query Source   : Excel Macros Google Group
Solution Type   : Functions and Formulas
Query by          : DM
Solution by       : Ashish Jain (MCAS;MCA;Lead Trainer, Success Electrons)

---

Query / Problem:
Ok, can someone help me out?  I am trying to create formula that is going to round my numbers to x.47 or x.97.
A1=$9.99
B1=75%
C1=A1-B1=$2.49
So what I am trying to do is to take discount from price and if it ends x.49 I would like to round it x.47 and if it ends x.99 I would like to round it to x.97. Does this make sense….so everything between x.51 and x.99 should be rounded to x.97 and everything between x.01 to x.49 should be rounded to x.47. 

---

Solution:

Assuming the contents as follows
Column A (Actual Price) – A1 = 9.99
Column B (Discount)      – B1 = 75%
Column C (Sale Price)    – C1 = ‘=A1*(1-B1)‘

The Solution would be
Column D (Tag Price)     – D1 = ‘=FLOOR(C2,1)+IF(CEILING(C2,1)-(C2)<0.5,0.97,0.47)‘

---

Example:

---