MS Excel HLOOKUP function for approximate data

Smoke Studios

In this tutorial, we’ll learn how to use the HLOOKUP function in Excel with approximate match. Unlike the exact match option, approximate HLOOKUP is useful when your data is arranged in rows and you want to find values closest to your lookup criteria.  We’ll go step by step through an example where HLOOKUP returns results by scanning across rows, helping you analyze data like grade ranges, sales targets, or tax brackets. By the end, you’ll clearly understand when and how to use HLOOKUP with TRUE (approximate match) in Excel.

Transcript

00:00Over here, you can see that I got a range of data from 0 to 500 and the labels cheap, affordable,

00:06and expensive in an ascending order. So now as per the value right here, I want it to disable

00:12cheap, affordable, or expensive. In that case, you can go ahead and press equals. And since this data

00:18is horizontally arranged, I'm going to use xlookup right here just like this, open up the bracket,

00:25and then take the sample right here from the two right here, press comma right here just like this,

00:31and select out this table range right here, press comma again, and I want the data from the second

00:39row, so I'm just going to add in two, and I want a range, so I just want an approximate match, so I'm

00:46just going to type in true right here, and press enter. So now this 251 says affordable, and then

00:5345 is cheap, and 70 or 700 is expensive as you can see.

Category

Transcript

Comments

Recommended