Answer / jit singh

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

Look_up_value can be a value, a reference, or a text string
and used to match between table array and data.

Col_index_num is the column index number of table_array. The
only difference between HLOOKUP and VLOOKUP is HLOOKUP use
row_index_num (row index number of table_array).

Range_lookup is a logical value that specifies whether you
want VLOOKUP to find the exact match or approximate match.

If TRUE, VLOOKUP will find an exact match or an approximate
match. If an exact match is not found, then the next largest
value that is less than lookup_value is returned.
If FALSE, VLOOKUP will find an exact match. If it's not
found, the error value #N/A is returned.

Here, you will learn how to use VLOOKUP and HLOOKUP
functions to get the value of base salary and transport fee
for each employee (based on his group).

1. Syntax:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Look_up_value:
In the screenshot, the fields "Group" are found in the
table_array and data. So the first data of field "Group" (in
B10) is the lookup_value for the first data of field "Base
Salary", likewise for the next and last data.

The range of table_array is $A$3:$C$6.

Col_index_num of Group is 1, Col_index_num of Base
Salary is 2, Col_index_num of Transport Fee is 3.



2. Formula of Base Salary data:
· Put the cell pointer in D10.
· Click Insert Function icon.
· In Or Select a category section, select Lookup &
Reference (see green rectangle in the screenshot below).
· On Select a function, choose VLOOKUP (see yellow
rectangle in screenshot below), then click OK.

3. In Function Arguments window:
- Lookup Value section: (If you cannot see the first
data of Group, just move/drag the Function Arguments
window). Click the first data of Group in B10 (see blue
rectangle in the screenshot below).
- Table Array section, Select range of table_array:
Click the first data of table in A3 then SHIFT+Click the
last data of table in E6. Press F4 button to absolute the
column and row of table_array (see purple rectangle in the
screenshot).
- Col_Index_Num section: type 2 (column index number
of Base Salary is 2). See red rectangle in the screenshot.
- Range_Lookup section, type: False (We will find an
exact match. If you will find an approximate match, just
type "TRUE" (see yellow rectangle in the screenshot), then
click OK.
· The result is 300.
· Copy from D10 to D10......D14.


Ads by Google

Trend Analysis in Excel
Excel Statistics & Trend Analysis Free 30 day trial.
www.ExcelCurveFitting.com

Try mini-graphs in Excel
Bissantz SparkMaker for Excel dashboards and reports -
free trial
www.bissantz.de

Excel
Today's Market News & Opinions by Udayan Mukherjee &
Other Experts
Moneycontrol.com/Excel

4. Formula of Transport Fee data:
· Put the cell pointer in E10.
· Click Insert Function icon.
· Repeat step 3- 8 of base_salary's formula. Only in
here, col_index_num, type 3.

5. Syntax:
=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) Look_up_value:
In upper screenshot, the first data of field "Group" (in B7)
is the lookup_value for the first data of field "Base
Salary," likewise for the next and last data.

The range of table_array is $B$1:$E$3.

Row_index_num of Group is 1.
Row_index_num of Base Salary is 2.
Row_index_num of Transport fee is 3.



6. Formula of Base Salary data:
- Put the cell pointer in D7.
- Click Insert Function icon.
In Or Select a category section, select Lookup &
Reference. On Select a function, choose HLOOKUP.
- Click OK.

7. In Function Arguments window:
- Lookup_Value section, click the first data of Group
in B7.
- In Table Array section, select range of table_array:
Click the first data of table in B1 then SHIFT+Click the
last data of table in E3. Press F4 button to absolute the
column and row of table_array.
- Row_Index_Num section: type 2 (row index number of
Base Salary is 2).
- Range_lookup: type FALSE (We will find an exact
match. If you will find an approximate match, just type
"TRUE"), then click OK.
- The result is 300. Copy from D7 to D7......D11.

8. Formula of Transport Fee data:
- Put the cell pointer in E7.
- Click Insert Function icon.
- Repeat step 3-8 of base_salary's formula. Only in
here, row_index_num: Type 3.

If you still don’t understand, you can delete the result
data (base salary or transport fee) then re-enter again.
Hope this helps!

Why do we need xaml? : xaml

0 Answers  

---

Hi, can anyone tell me what is the difference between SYSVOL and NTDS folder in AD? I know SYSVOL is for shared information between Domain controllers, but isn't AD database shared as well? Don't they use the same DB which is stored in NTDS folder? I know SYSVOL size is much smaller than NTDS', but what is it for? Does it contain a part of NTDS folder? If so, what namely? Thanks

0 Answers   Microsoft, Orbit Corporation,

---

What is the latest microsoft?

0 Answers  

---

Axapta interview in wipro: join types? delete actions? posting for sales order what class used? write method? insert ,update methods? diff between inner join and exist join? diff between doupdate and update method? ,, ,, validate write and validate field method?

0 Answers   Wipro,

---

What are the microsoft apps?

0 Answers  

---

How does xaml relate to xml-based web service transport protocols (xp, soap, ebxml transport)? : xaml

0 Answers  

---

What is DLL hell problem?

0 Answers   CitiusTech,

---

Can you explain about browser link?

0 Answers  

---

Can we do interactive sort for more than one field in ssrs?

1 Answers   Baidu, Bank Of America, HCL,

---

How do I get rid of microsoft compatibility telemetry?

0 Answers  

---

Can I set folder qouta on server 2003 SP1 enterprise edition setup as file server? Can I use FSRM on this or do I really need to update my server to 2003 R2? Thanks.

0 Answers  

---

Why to use DNS? DIfference between differtial backup and Incremental backup with example ? what is difference between arp and rarp ? What is difference beteen windows 2003 and windows 2008 server? What is diff betwen exchange server 2003 and exchange server 2007? Which protocol work behind active directory?

1 Answers   Baidu, Precision, Titan,

---