Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLookup or Cond. Format?
I am trying to create a formula or something that will go from a value in sheet and go to sheet 2 and find it in column A. Depending on where it is in column A, then choose the value that is in the column in B and multiply it by that value. .............. Ex: Sheet 1: (M11) contains the value 2 Sheet 2: contains a list of levels in column A that range from 1 - 30 Sheet 2: Columns B - D contain the cost of each item depending on level in Column A. Sheet 1: (M12) will go and verify number entered into (M11) and find the value for level and cost of column B, then mulitly it by the value that you enterin M12. I know I may be explaining it badly but just need something to go through the table or listing find the value of the level I entered in another cell, find the cost associated for that level, and multiply it by the number that I enter in the cell. 0 30 240 600 1 30 240 600 2 30 240 600 3 30 240 600 4 29 232 580 5 29 232 580 6 29 232 580 7 28 224 560 8 28 224 560 9 28 224 560 10 27 216 540 11 27 216 540 12 27 216 540 13 27 216 540 14 26 208 520 15 26 208 520 16 26 208 520 17 25 200 500 18 25 200 500 19 25 200 500 20 24 192 480 21 24 192 480 22 24 192 480 23 24 192 480 24 23 184 460 25 23 184 460 26 23 184 460 27 22 176 440 -- mauddib13 ------------------------------------------------------------------------ mauddib13's Profile: http://www.excelforum.com/member.php...o&userid=25785 View this thread: http://www.excelforum.com/showthread...hreadid=522039 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLookup or Cond. Format?
I noticed, you multiple identical rows, what is the purpose of that ? Can you give another explaination this time use the numbers that are in you example -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=522039 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HLookup or Cond. Format?
Hi,
You need to use v-lookup for this. Check out how to use v-lookup in the excel help content. I will explain what you need to enter into the functions arguments below: Lookup_value - click on cell M11 in sheet 1 (which if you explained properly has the value 2 in it) Table_array - this is what place in which you want to search for the "Lookup value" (cell m11). You can either highlight the whole of sheet 2 or just the table of information within sheet 2. Col_index_num - This is asking you that once it has found the value from cell m11 in sheet 2 which column do you want to return. Like you said you don't want it to return the actual value in cell m11 (which would probably be column A). You want the value from column B. So type in 2 into this line. Range_lookup - finally this wants to know if it's looking for the exact value in cell m11 or just any cell in sheet 2 with the the same numbers as cell m11 (in other words if cell m11 has a value of 2 then the lookup can search and return a cell in sheet 2 that has a value of 129 because it has a 2 in it). If you want the exact value (and you do) then type FALSE. That should do it. Carl |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format Not Working | Excel Discussion (Misc queries) | |||
Cond Format & helper-cell based "duplicate rec" tricked by content | Excel Discussion (Misc queries) | |||
can't format cell - have tried unlocking and unprotecting | Excel Discussion (Misc queries) | |||
why does currency format change to number format? | Excel Discussion (Misc queries) | |||
Keep custom format in new worksheet | Excel Discussion (Misc queries) |