Excel functions VLOOKUP and HLOOKUP - formula examples

Would you please provide working examples of VLOOKUP and HLOOKUP functions ? The formulas I have created are not working for some reason.
12 Dec 2020 at 02:55 PM
0give a positive ratinggive a negative ratingshow more
NicoleNicole
Hi,

VLOOKUP function in the following example is used to get the unit price from the second table, that is created on another sheet.

The first table:

excel vlookup function formula table items

The second table on another sheet:

excel vlookup function formula table prices

To get unit price from the second table for C2, the formula should look like:

=VLOOKUP(A2;Sheet2!$A$2:$D$4;4;FALSE)

A2 - lookup value
Sheet2!$A$2:$D$4 - table array, which describes the fixed location of the second table
4 - the column index number, which describes the column where the value is located
FALSE - used to apply the exact match

When you are using the tables as objects, the formula in C2 can also look like:

=VLOOKUP([@Item],Table2,4,FALSE)

HLOOKUP function in the following example is used to get the percentage rate from the first table, to calculate the selling price:

excel hlookup function formula

To get percentage rate from the first table for D7, the formula should look like:

=HLOOKUP(B7;$B$1:$E$2;2;FALSE)

B7 - lookup value
$B$1:$E$2 - table array, which describes the fixed location of the first table
2 - the row index number, which describes the row where the value is located
FALSE - used to apply the exact match
13 Dec 2020 at 01:57 PM
0give a positive ratinggive a negative ratingshow more
TimTim
Share on FacebookShare on TwitterShare on LinkedInSend email
Follow us on Facebook
2022 AnswerTabsTermsContact us