Learn to use VLOOKUP in Excel by theexcelclub

View this thread on: hive.blog | peakd.com | ecency.com

excel · @theexcelclub · Jan 22 '19

$1.18

Learn to use VLOOKUP in Excel

<p>The VLOOKUP and HLOOKUP formula in Excel allow you look up a value in one column or row and return a corresponding value from a different column or row. VLOOKUP is a vertical lookup and HLOOKUP is a horizontal lookup

The table of data below contains 4 columns.&Acirc;&nbsp; A Product Number, the Supplier, the quantity in stock and the cost price.&Acirc;&nbsp; With a small table of data like this it is easy with the eye to look at a product number and see who the supplier is, how much is in stock and how much it cost.&Acirc;&nbsp; But imagine you had a large table of data, scrolling through it, or using the filters to find the sock code is not efficient.&Acirc;&nbsp; This is where VLOOKUPs are very powerful.

<img src="http://theexcelclub.com/wp-content/uploads/2019/01/3-1.png" alt="An introduction to using Vlookups" width="664" height="231"><br/></p><h2>VLOOKUP Syntax</h2>
The syntax for VLOOKUP is:

VLOOKUP = (lookup value, table array, column index, range lookup)

Lookup value is the value you wish to look up.&Acirc;&nbsp; This value must be in the far most left column of the table

Table array is the table in which you want to search

Column index is the column number you wish to return the data from

Range lookup offers a true or false selection.&Acirc;&nbsp; Where true is an exact match and will only return a value where an exact match is found. When using an Approx. match (false), items in the table must be sorted from lowest to highest, or in alphabetical order.
<h2>Constructing a VLOOKUP formula</h2>
Using the above table of data, given <br /><center><hr/><em>Posted from my blog with <a href='https://wordpress.org/plugins/steempress/'>SteemPress</a> : http://theexcelclub.com/learn-to-use-vlookup-in-excel/ </em><hr/></center>

👍 steempress-io, helpie, paulag, abh12345, eonwarped, doomsdaychassis, crimsonclad, erikaflynn, sbi6, nealmcspadden, etcmike, ghostdylan, steempress, clumsysilverdad, steemitboard, theexcelclub, swisswitness, camuel, hiddenblade, solominer, tea-man, icondark, stmpay, daan, wolfnworbeikood, bluerobo, seanlloyd, bluesniper, clayboyn, evernoticethat, redrica, infamousit, mcoinz79, lordbutterfly, jayna, clubfungus, musicvoter, trevorpetrie, silentscreamer, gamsam, pechichemena, amn, yestermorrow, alvin0617, yes-please, carrieallen, anarcho-andrei, siomarasalmeron, discordiant, foxyspirit, luisferchav, hazem91, amymya, warpedpoetic, steemexpress, mountainjewel, veckinon, lillywilton, tonygreene113, soulturtle, socent, changjia, javicuesta, kbr, and 39 others

properties (23)vote details (103)

voter	rshares	pct
daan	5,662,341,032	3%
steempress	15,790,345,849	3.5%
elamental	189,350,450	1%
anarcho-andrei	1,579,848,988	7.5%
etcmike	18,841,093,636	10%
abh12345	193,244,847,605	33%
clayboyn	4,966,866,450	7%
erikaflynn	22,616,320,177	50%
steemitboard	13,692,238,971	1%
omitaylor	73,592,703	2%
crimsonclad	26,121,970,759	6%
paulag	286,951,906,704	100%
jonmagnusson	141,275,992	0.5%
cobloc	68,154,139	12.5%
jayna	3,899,178,628	3.75%
mcoinz79	4,051,721,263	1%
discordiant	1,355,287,853	6%
dernan	553,274,718	100%
amymya	913,545,959	2.5%
krazypoet	304,242,828	2.5%
clumsysilverdad	13,804,910,292	7%
redrica	4,208,493,175	5.5%
evernoticethat	4,926,189,205	25%
trevorpetrie	2,942,714,640	25%
tonygreene113	642,053,663	1.75%
carrieallen	1,699,456,493	5%
hz432creations	97,629,178	12.5%
melavie	409,248,620	5%
infamousit	4,131,838,510	25%
art-mess	331,384,436	12.5%
socent	618,336,994	15%
eonwarped	48,909,841,606	9%
wolfnworbeikood	5,580,739,310	13%
seanlloyd	5,415,908,680	17%
pechichemena	2,224,999,288	5%
kernelillo	346,107,020	12.5%
helpie	410,828,345,267	25%
markaustin	397,772,855	5%
luisferchav	1,003,508,631	12.5%
soulturtle	620,695,242	5%
mountainjewel	766,609,905	0.5%
carpedimus	380,985,578	12.5%
gamsam	2,272,691,098	0.75%
adesojisouljay	64,504,574	5%
nealmcspadden	21,367,495,105	100%
grizzle	194,079,492	1%
silentscreamer	2,655,720,496	12.5%
warpedpoetic	909,020,577	3.75%
hazem91	944,737,807	5%
amn	2,124,758,689	100%
verhp11	73,497,547	1%
juandvg	211,867,225	12.5%
siomarasalmeron	1,378,193,195	12.5%
spawnband	532,970,273	5%
doomsdaychassis	32,292,475,450	25%
foxyspirit	1,086,876,503	5%
lordbutterfly	4,022,917,964	5%
hiddenblade	8,774,038,087	20%
steempress-io	1,414,007,708,510	3.5%
veckinon	707,550,135	12.5%
djdarkstorm	506,794,944	25%
camuel	9,076,417,069	20%
samprock	321,918,437	2.5%
javicuesta	605,315,306	25%
solominer	8,171,776,957	23.75%
archaimusic	118,049,866	10%
musicvoter	3,328,346,433	1%
zaxan	273,618,265	5%
kbr	571,647,711	6.25%
clubfungus	3,895,136,385	5%
free-reign	513,375,034	12.5%
misterro	132,813,778	25%
shookriya	492,982,105	5.55%
musicvoter2	69,009,725	10%
frejafri	322,662,725	2.5%
tea-man	7,281,185,995	50%
alchemicjourney	523,145,048	100%
fotostef	208,557,460	1%
swisswitness	10,401,124,504	50%
marysemciver	241,549,204	10%
sbi6	21,743,982,213	4.76%
tangofever	303,796,666	23%
lillywilton	673,241,665	20%
yestermorrow	2,017,468,511	6.25%
preventsuicide	106,954,084	3.75%
icondark	7,056,112,601	100%
emsteemians	102,075,334	10%
theexcelclub	13,565,164,009	100%
frankduna	524,537,806	100%
yes-please	1,870,875,573	50%
changjia	609,203,297	12.9%
raoufwilly	450,656,362	7.5%
alvin0617	1,985,262,235	25%
steemexpress	781,493,201	1.51%
unconsumed	70,580,701	100%
stmpay	6,757,523,817	2%
bluerobo	5,562,506,835	100%
bluesniper	5,000,079,701	0.4%
mrsbozz	220,720,356	5%
oh-high-mark	383,186,951	12.5%
butt-memes	511,976,108	12.5%
zaxan-blog	101,489,003	25%
ghostdylan	17,210,384,607	100%

@coop78 · Aug 10 '19

$0.30

k4749y128n

What formula did you use to return the customer? =VLOOKUP(A8,A1:C4,2,FALSE)

What formula did you use to return the Invoice value? =VLOOKUP(A8,A1:C4,3,FALSE)

What would happen your formula if you entered a new column of data between the invoice number and the Customer in the lookup table?  The value returned for Customer is unaffected, but the value returned for Invoice Value changes to 0.  The formula would have to be updated to reflect the change in the table array and column index number.

👍 paulag, theexcelclub, steemcommunity

properties (23)vote details (3)

voter	rshares	pct
paulag	636,764,103,576	100%
steemcommunity	114,097,597,560	100%
theexcelclub	144,653,100,601	100%

@dernan · Jan 23 '19

$0.11

c6pjw0mror

What formula did you use to return the customer?
=VLOOKUP(A7,A1:C4,2,FALSE)

What formula did you use to return the Invoice value?
=VLOOKUP(A7,A1:C4,3,FALSE)

What would happen your formula if you entered a new column of data between the invoice number and the Customer in the lookup table?
if you entered a new column the vlookup would no longer work because the columns move so the column index number used in the formulas would no longer be correct

👍 paulag, ghostdylan

properties (23)vote details (2)

voter	weight	wgt%	rshares	pct	time
paulag	0 B		288,547,964,906	100%
ghostdylan	0 B		0	100%

@paulag · Jan 23 '19

Nice work @dernan, you got all of this correct.  There is actually a LOOKUP skills test available free on the website if you would like to try it out  http://theexcelclub.com/excel-skills-testing/

properties (22)

`author`	paulag
`permlink`	re-dernan-blm55j1rbu0mcz8-20190123t123942544z
`category`	excel
`json_metadata`	{"tags":["excel"],"users":["dernan"],"links":["http://theexcelclub.com/excel-skills-testing/"],"app":"steemit/0.1"}
`created`	2019-01-23 12:39:42
`last_update`	2019-01-23 12:39:42
`depth`	2
`children`	0
`last_payout`	2019-01-30 12:39:42
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.000 HBD
`curator_payout_value`	0.000 HBD
`pending_payout_value`	0.000 HBD
`promoted`	0.000 HBD
`body_length`	196
`author_reputation`	274,264,287,951,003
`root_title`	"Learn to use VLOOKUP in Excel"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 HBD
`percent_hbd`	10,000
`post_id`	78,811,567
`net_rshares`	0

@steemitboard · Jan 23 '19

Congratulations @theexcelclub! You have completed the following achievement on the Steem blockchain and have been rewarded with new badge(s) :

<table><tr><td>https://steemitimages.com/60x70/http://steemitboard.com/@theexcelclub/votes.png?201901230549</td><td>You made more than 500 upvotes. Your next target is to reach 600 upvotes.</td></tr>
</table>

<sub>_[Click here to view your Board](https://steemitboard.com/@theexcelclub)_</sub>
<sub>_If you no longer want to receive notifications, reply to this comment with the word_ `STOP`</sub>


To support your work, I also upvoted your post!


> Support [SteemitBoard's project](https://steemit.com/@steemitboard)! **[Vote for its witness](https://v2.steemconnect.com/sign/account-witness-vote?witness=steemitboard&approve=1)** and **get one more award**!

properties (22)

`author`	steemitboard
`permlink`	steemitboard-notify-theexcelclub-20190123t061002000z
`category`	excel
`json_metadata`	{"image":["https://steemitboard.com/img/notify.png"]}
`created`	2019-01-23 06:10:00
`last_update`	2019-01-23 06:10:00
`depth`	1
`children`	0
`last_payout`	2019-01-30 06:10:00
`cashout_time`	1969-12-31 23:59:59
`total_payout_value`	0.000 HBD
`curator_payout_value`	0.000 HBD
`pending_payout_value`	0.000 HBD
`promoted`	0.000 HBD
`body_length`	806
`author_reputation`	38,975,615,169,260
`root_title`	"Learn to use VLOOKUP in Excel"
`beneficiaries`	`[]`
`max_accepted_payout`	1,000,000.000 HBD
`percent_hbd`	10,000
`post_id`	78,799,093
`net_rshares`	0

@steemitboard · Jan 24 '19

<center>[![](https://steemitimages.com/175x175/http://steemitboard.com/@theexcelclub/level.png?201901241545)](http://steemitboard.com/@theexcelclub)
**Congratulations @theexcelclub**!
You raised your level and are now a **Minnow**!</center>

> Support [SteemitBoard's project](https://steemit.com/@steemitboard)! **[Vote for its witness](https://v2.steemconnect.com/sign/account-witness-vote?witness=steemitboard&approve=1)** and **get one more award**!

properties (22)