Excel VLOOKUP Formula/Function

Question: – What is VLOOKUP Function / Formula?

Question: – Were VLOOKUP Function / Formula used?

Here is the answer of your questions:-

The VLOOKUP function / formula is one of several lookup functions supported by Microsoft Excel. It is used when you want to “LOOKUP” a value from a table based on some key value. The help provide by Excel is very good. VLOOKUP is a VERTICAL lookup function (the function scans down the target column looking for your key), HLOOKUP is a horizontal lookup function (the function scans across the target row looking for your key).

Some info from the Excel Help menu on VLOOKUP function / formula

VLOOKUP function / formula

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. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find.

Syntax

VLOOKUP function / formula (lookup_value,table_array,col_index_num,range_lookup)

Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

Table array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

If range lookup is TRUE, the values in the first column of table array must be placed in ascending order: …, -2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range lookup is FALSE, table array does not need to be sorted.

You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.

The values in the first column of table array can be text, numbers, or logical values.

Uppercase and lowercase texts are equivalent.
Col_index_num is the column number in table array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! Error value; if col_index_num is greater than the number of columns in table array, VLOOKUP returns the #REF! Error value.

Range lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

Remarks

If VLOOKUP can’t find lookup value, and range lookup is TRUE, it uses the largest value that is less than or equal to lookup value.

If lookup value is smaller than the smallest value in the first column of table array, VLOOKUP returns the #N/A error value.

If VLOOKUP can’t find lookup value, and range lookup is FALSE, VLOOKUP returns the #N/A value.

Examples

**key spreadsheet illustration here; go to actual Help to see it**

On the preceding worksheet, where the range A4:C12 is named Range:

VLOOKUP(1,Range,1,TRUE) equals 0.946

VLOOKUP(1,Range,2) equals 2.17

VLOOKUP(1,Range,3,TRUE) equals 100

VLOOKUP(.746,Range,3,FALSE) equals 200

VLOOKUP(0.1,Range,2,TRUE) equals #N/A, because 0.1 is less than the smallest value in column A

VLOOKUP(2,Range,2,TRUE) equals 1.71

button1 Excel VLOOKUP Formula/Function

Posted under Microsoft, Ms-Office, Uncategorized

This post was written by Rahul Nagar on December 10, 2008

Tags:

Keyboard shortcuts for Run Command

If you are tired of going through the Windows Menu to open a program in Windows or you dont know how to directly enter into Windows Root Directory, Administrative Tools, Network Connections, Disk Management, Performance Monitor and more then you need to have the Run Prompt Shortcut Keys to open These Applications.
This post will help you find all the Shortcut Keys for the Windows Run Prompt and make you like a bit easier.

Shortcut Keys

Command

%appdata%

Application Data Folder

%homedrive%

Home Directory Drive

%homepath%

Home Directory

%homeshare%

Shared Home Directory

%programfiles%

Open Program Files folder

%systemdrive%

Windows Root Drive

%temp%

Temporary Folder

%windir%

Windows Root Directory

ac3filter.cpl

Opens AC3 Filter (If Installed)

access.cpl

Accessibility Controls

access.cpl

Accessibility Controls

appwiz.cpl

Add/Remove Programs

calc

Calculator

certmgr.msc

Certificate Manager

charmap

Character Map

chkdsk

Check Disk Utility

ciadv.msc

Indexing Service

cleanmgr

Disk Cleanup Utility

cliconfg

SQL Client Configuration

clipbrd

Clipboard Viewer

cmd

Command Prompt

compmgmt.msc

Computer Management

controladmintools

Administrative Tools

controlcolor

Display Properties

(w/Appearance Tab Preselected)

controldesktop

Display Properties

controlfolders

Folders Properties

controlfonts

Fonts

controlkeyboard

Keyboard Properties

controlmouse

Mouse Properties

controlnetconnections

Network Connections

controlpanel

Open Control Panel

controlprinters

Printers and Faxes

controlschedtasks

Scheduled Tasks

dcomcnfg

Component Services

ddeshare

DDE Shares

desk.cpl

Display Properties

devmgmt.msc

Device Manager

dfrg.msc

Disk Defragment

directx.cpl

Direct X Control Panel (If Installed)*

diskmgmt.msc

Disk Management

diskpart

Disk Partition Manager

drwtsn32

Dr. Watson System Troubleshooting

dxdiag

Direct X Troubleshooter

eudcedit

Private Character Editor

eventvwr.msc

Event Viewer

excel

Run Microsoft Excel (if installed)

findfast.cpl

Findfast

firefox

Run Firefox (if installed)

firewall.cpl

Windows Firewall

fonts

Fonts Folder

freecell

Free Cell

fsmgmt.msc

Shared Folders

fsquirt

Bluetooth Transfer Wizard

gpedit.msc

Group Policy Editor (XP Prof)

hdwwiz.cpl

Add Hardware Wizard

iexplore

Run Internet Explorer

iexpress

Iexpress Wizard

inetcpl.cpl

Internet Properties

intl.cpl

Regional Settings

ipconfig

IPConfiguration

(DisplayConnectionConfiguration)

ipconfig/all

IP Configuration

(Display Connection Configuration)

ipconfig/displaydns

IP Configuration

(Display DNS Cache Contents)

ipconfig/flushdns

IP Configuration

(Delete DNS Cache Contents)

ipconfig/registerdns

IP Configuration

(Refreshes DHCP & Re-Registers DNS)

ipconfig/release

IP Configuration

(Release All Connections)

ipconfig/renew

IP Configuration

(Renew All Connections)

ipconfig/setclassid

IP Configuration

(Modifies DHCP Class ID)

ipconfig/showclassid

IP Configuration

(Display DHCP Class ID)

javaws

Java Control Panel (If Installed)

joy.cpl

Game Controllers

jpicpl32.cpl

Java Control Panel (If Installed)

logoff

Log Off Windows

lusrmgr.msc

Local Users and Groups

magnify

Windows Magnifier

main.cpl

Mouse Properties

mmsys.cpl

Sounds and Audio

msconfig

System Configuration Utility

mshearts

Hearts Card

mspaint

MS-Paint

mstsc

Remote Desktop

ncpa.cpl

Network Connections

netsetup.cpl

Network Setup Wizard

notepad

Notepad

ntmsmgr.msc

Removable Storage

ntmsoprq.msc

Removable Storage Operator Requests

nusrmgr.cpl

User Account Management

nvtuicpl.cpl

Nview Desktop Manager (If Installed)

odbccp32.cpl

ODBC Data Source Administrator

osk

On Screen Keyboard

outlook

Run Microsoft Outlook (if installed)

packager

Object Packager

password.cpl

Password Properties

perfmon

Performance Monitor

perfmon.msc

Performance Monitor

powercfg.cpl

Power Configuration

printers

Printers Folder

QuickTime.cpl

Quicktime (If Installed)

regedit

Registry Editor

regedit32

Registry Editor

rsop.msc

Resultant Set of Policy

secpol.msc

Local Security Settings

services.msc

Services

sfc

SystemFileCheckerUtility

(ReturntoDefaultSetting)

sfc/cachesize=x

System File Checker Utility

(Set Cache Size to size x)

sfc/purgecache

System File Checker Utility

(Purge File Cache)

sfc/revert

System File Checker Utility

(Return to Default Setting)

sfc/scanboot

System File Checker Utility

(Scan On Every Boot)

sfc/scannow

System File Checker Utility

(Scan Immediately)

sfc/scanonce

System File Checker Utility

(Scan Once At Next Boot)

shutdown

Shutdown Windows

shutdown-r

Restart Windows

sigverif

File Signature Verification Tool

spider

Spider Solitare

sticpl.cpl

Scanners and Cameras

sysdm.cpl

System Properties

sysedit

System Configuration Editor

syskey

Windows System Security Tool

taskmgr

Task Manager

telephon.cpl

Phone and Modem Options

telnet

Telnet Client

timedate.cpl

Date and Time

tourstart

Windows XP Tour Wizard

utilman

Utility Manager

verifier

Driver Verifier Utility

winchat

Microsoft Chat

winmine

Minesweeper

winword

Run Microsoft Word

(if installed)

wmimgmt.msc

Windows Management

Infrastructure

wordpad

Run WordPad

write

WordPad

wscui.cpl

Security Center

wuaucpl.cpl

Automatic Updates

wupdmgr

Windows Update Launches

button1 Keyboard shortcuts for Run Command

Posted under Uncategorized

This post was written by Rahul Nagar on November 3, 2008