Ms excel: if function (WS)

MS Excel: Two-Dimensional Lookup (Example #1)

This Excel tutorial explains how to perform a two-dimensional lookup (with screenshots and step-by-step instructions). This is example #1.

Question: I'm trying to reference a particular cell within an xy axis chart and can't find the formula or function that allows me to do so.

For example A1 needs to equal where row 12 intersects column F on a chart.

I know the lookup function can get me a value from a known array of values located in the corresponding column, but I can't get it to figure from an array of columns. Can you help?

Answer: In effect, what we are trying to do is perform a 2-dimensional lookup in Excel. To find a value in Excel based on both a column and row value, you will need to use both a vlookup function and a match function.

Let's look at an example to see how you would use this function in a worksheet:

In the spreadsheet above, we have a listing of products (Oranges, Apples, Bananas, Pineapples, Watermelons) and a listing of quantity columns (5 lbs, 10 lbs, 15 lbs, and 20 lbs). What we want to do is find the correct value based on a product and quantity combination.

In the first case, we want to find the price/lb for 10 lbs of oranges. To find the price/lb, we've entered the following formula into cell D17:

=VLOOKUP(B17, $B$8:$F$13, MATCH(C17, $B$8:$F$8, 0), FALSE)

This formula returns the value of $4.80.

In the second example, we are looking for the price/lb for 5 lbs of bananas. We've entered the following formula into cell D18:

=VLOOKUP(B18, $B$8:$F$13, MATCH(C18, $B$8:$F$8, 0), FALSE)

This formula returns the value of $1.50.

MS Excel: Two-Dimensional Lookup (Example #2)

This Excel tutorial explains how to perform a two-dimensional lookup (with screenshots and step-by-step instructions). This is example #2.

Question: I need to find the value on a chart (see below). The only problem is that I can have a material value that is not an exact match to a value on the chart. In this case, I need to round down and find the next smaller amount. For example, if I have 8 lbs of materials, it should return the value of 1 lbs of materials.

Answer: In effect, what we are trying to do is perform a 2-dimensional lookup in Excel. To find a value in Excel based on both a column and row value, you will need to use both a vlookup function and a match function.

Let's look at an example to see how you would use this function in a worksheet:

In the spreadsheet above, we have a listing of materials (in lbs.) and a listing of shift (1 through 6). What we want to do is find the correct value based on an amount of materials and a shift combination.

In the first case, we want to find the chart value for 1 lbs of materials and 1 shift. We've entered the following formula into cell F18:

=VLOOKUP(D18, $C$4:$I$14, IF(ISNA(MATCH(E18, $C$4:$I$4, 0)), 7, MATCH(E18, $C$4:$I$4, 0)), TRUE)

This formula returns 0.7 or 70%.

The last parameter on the VLOOKUP function is set to TRUE. This means that if the VLOOKUP does not find an exact match for the materials, it will look for the next smaller value. (In other words, rounding down)

Also, you'll find a 7 in the middle of the formula. This means that if you don't find a match for the shift value, it will use column (i) which is the 7th column. You'll have to modify this if you add more shifts.

In the second example, we are looking for the chart value for 2 lbs of materials and 8 shifts. We've entered the following formula into cell F19:

=VLOOKUP(D19, $C$4:$I$14, IF(ISNA(MATCH(E19, $C$4:$I$4, 0)), 7, MATCH(E19, $C$4:$I$4, 0)), TRUE)

This formula returns 0.45 or 45%.

In this example, an 8th shift can not be found, so the formula uses column (i) to derive the value.

In the final example, we are looking for the chart value for 3001 lbs of material and 6 shifts. We've entered the following formula in cell F20:

=VLOOKUP(D20,$C$4:$I$14,IF(ISNA(MATCH(E20,$C$4:$I$4,0)), 7,MATCH(E20,$C$4:$I$4,0)),TRUE)

This formula returns 0.01 or 1%.

MS Excel: Two-Dimensional Lookup (Example #3)

This Excel tutorial explains how to perform a two-dimensional lookup (with screenshots and step-by-step instructions). This is example #3.

Question: I need to find the value on a chart (see below). The only problem is that I can have a quantity value that is not an exact match to a value on the chart. In this case, I need to round down and find the next smaller amount. For example, if I have 51 lbs as a quantity, it should return the value for 50 lbs.

Answer: In effect, what we are trying to do is perform a 2-dimensional lookup in Excel. To find a value in Excel based on both a column and row value, you will need to use both a hlookup function and a match function.

Let's look at an example to see how you would use this function in a worksheet:

In the spreadsheet above, we have a listing of materials (Oranges, Apples, Bananas, Pineapples, Watermelons) and a listing of quantity columns (50 lbs, 100 lbs, 200 lbs, 500 lbs, 1000 lbs). What we want to do is find the correct value based on a material and quantity combination.

In the first case, we want to find the chart value for 50 lbs of apples. We've entered the following formula into cell D13:

=HLOOKUP(C13, $B$4:$G$9, MATCH(B13, $B$4:$B$9, 0), TRUE)

This formula returns $1.43.

The last parameter on the HLOOKUP function is set to TRUE. This means that if the HLOOKUP does not find an exact match for the quantity, it will look for the next smaller value. (In other words, rounding down)

In the second example, we are looking for the chart value for 1200 lbs of bananas We've entered the following formula into cell D14:

=HLOOKUP(C14, $B$4:$G$9, MATCH(B14, $B$4:$B$9, 0), TRUE)

This formula returns $0.97.