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
0pnshow 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
0pnshow more
TimTim
Share on FacebookShare on TwitterShare on LinkedInSend email
Follow us on Facebook & Twitter
2021 AnswerTabsTermsContact us