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 and a match function.
Let's take a look at an example:
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.
***********************************************************************
Question: I need to find the value on a chart (see below). I have a materials value and a shifts value. 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 and a match function.
Let's take a look at an example:
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%.
******************************************************************
Question: I need to find the value on a chart (see below). I have a materials value and a quantity value. 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 and a match function.
Let's take a look at an example:
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.
******************************************************************
Question: In Excel, I want to match the values in two columns on sheet A (size and frequency) to a column and a row on sheet B (size and frequency) to return a value on sheet B.
For example, size=2 and frequency=2 on Sheet A would return $23.00 from the chart on Sheet B (size=2, frequency=2). I would like to have these values displayed in Column D on sheet A.
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 the vlookup function and the match function.
Let's take a look at an example.
Download Excel spreadsheet (as demonstrated below)
In the spreadsheet above, we have a chart on sheet B with Size and Frequency.
Then on Sheet A, we have Size and Frequency values in columns A and B. We want to return in column D, the correct value from the chart in Sheet B using these Size and Frequency values.
So if you looked up a Size=2 and Frequency=2 in the chart in sheet B, it should return $23.00. To do this, we will use the following formula in cell D2:
=VLOOKUP(A2,B!$A$2:$G$8,MATCH(B2,B!$A$2:$G$2,0),FALSE)
This formula returns $23.00.
In cell D3, if we wanted to look up a Size=2 and Frequency=1, it should return $22.00. To do this, we will use the following formula:
=VLOOKUP(A3,B!$A$2:$G$8,MATCH(B3,B!$A$2:$G$2,0),FALSE)
This formula return $22.00.
No comments:
Post a Comment