Using HLOOKUP in calculations

In my example above, argument 1 of my index function (range) is my entire lookup table. Argument 2 (Row Index) is my Match formula. Argument 3 (column index) is “2” because the value I want (price) is in the second column of my range. Argument 4 is “1” because I only selected one range. For the match formula, argument 1 is a cell reference to “Apples”. This is the word I am searching for. Argument 2 is the column in which it will find the word “Apples” in my lookup Table. This is a 1 dimensional array. Either a single column or a single row. Argument 3 is “find exact value” because it is searching for text.

Using Match on its own in this example would produce a result of 2, because Apples is in the second cell of the column in which it is searching. So my index formula will give me the value in the second row, and second column (because I used 2 as my column reference) of my range (entire lookup table) which is $2.00.

In this example, I only used Match as the row reference, and used a constant (“2”) as my column reference, because I knew the price I am trying to look up is in the second column of my range. But if I was adding columns to my lookup table and it may not stay the second column, I could use Match twice, as both my row index and column index. If I used it as my column index, I would search for the word price in row 1 to identify the column number in which it would find price.

Link to example: https://www.icloud.com/numbers/0-3uZlurvj0kXx3wSZadv6LmA#Blank_24

Hi Zarquon,

It would be useful to know how you are using these tables. I think they will want to be reorganized for clarity and ease of use. Numbers has many more rows available than columns and so the program makes it easier to do things when data is organized more like Bismarck's Price list table. It will be easier to read and more compact also.

It looks like you are trying to track sales of different scouts. Where is this data originating? Is it being entered by you or is it being brought over from another table? I would suggest a single table devoted to input, a lookup table for your prices and a report table. This will break up the tasks you are asking of Numbers and so simplify your formulas. It should also be easier to interpret your data.

quinn

Yes, there are so many functions in Numbers (even more in Excel) that it can be hard to find the right one for the job at hand, even if one remembers it exists!

Let us know if you have any trouble implementing SUMPRODUCT.

You can find more information on the function here.

SG

Here is a generic example:

This formula contains 3 functions. They are:

Match will return the position of a value in an array. For example, if you have a column of 10 cells, numbered from 10 to 19, and you use Match to look up 12, it will return a result of 3, as 12 is in the third cell. Use match as your row reference if looking up something in a column list, and as a column reference if looking up something in a row list. The other reference will be the row or column in which the value you are trying to look up will be.

Hi Bismarck,

I am also a big fan of INDEX/MATCH for lookups. We can do everything any of the LOOKUP variants do and with more flexibility.

I suggest that IFERROR is worth avoiding whenever possible. It can mask issues and there is usually another way to accomplish what is needed.

One way to test a cell for content is with LEN, which will count the characters in a cell. We could construct our formula like this:

IF(LEN(source-string)>0,INDEX/MATCH,"")

This would prevent an error flag only if there was an empty cell. Otherwise we can see what is going on.

quinn

It almost never pays to have a big long formula like that.

In this case, is there a reason you are not using SUMPRODUCT? You can do something like this:

The trick is to have the ranges the same size in both tables.

The formula in F2 of my example, filled down, is:

=SUMPRODUCT(B2:E2,Prices::$B$2:$E$2)

If you change the order (or number of columns) in the main table, you can simply change the order (and number of columns) in the Prices table to match.

Substitute ; for , in the formula if your regions uses , as a decimal separator.

SG

I recommend Index-Match for all lookups. It’s the most efficient way in terms of system resources - your computer can calculate it it faster than using VLOOKUP, HLOOKUP, or LOOKUP.

Certainly. Can you share a pic of your file? I can customize it to what you need. It’s a bit unclear what exactly you are trying to do. Or I can come up with a generic example.

Hi SG,

I always appreciate you ability to cut thru and find simple and elegant solutions.

quinn