| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 48898 | 2004-09-04 06:22:00 | excel formula | bpt1 (419) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 269189 | 2004-09-05 00:40:00 | We might need a few more details, just like before, to solve the latest problem although I suspect there is a problem with variables within your lookup function, if you are using one. Unfortunately I am going to have to recommend Microsoft : use the help within Excel (after turning off the really annoying help wizard) and look for how to use the vlookup and hlookup functions. You might find your answer in there. | andrew93 (249) | ||
| 269190 | 2004-09-05 00:49:00 | The problem with HLOOKUP is that it just checks the first row. I need to check the whole table, and then when a matching value is found, return the value from the bottom row in the same column as the matching value Does that make sense? |
bpt1 (419) | ||
| 269191 | 2004-09-05 00:52:00 | Maybe I could combine HLOOKUP with the IF function and check each row seperately. | bpt1 (419) | ||
| 269192 | 2004-09-05 00:57:00 | Yes, it makes sense (penny drops), I'm not sure about this one but you won't be using the lookup functons (sorry for the dud recommendation) - I will have a hunt around and in the meantime let's see if either parry or rugila can come up with something. | andrew93 (249) | ||
| 269193 | 2004-09-05 01:06:00 | If this is the same spreadsheet as before, could you not do an HLOOKUP on the value stored in A2 across the first row of data (H1 to O1) and then retrieve the value from the very bottom of the matching column? Wouldn't this have the same effect? | andrew93 (249) | ||
| 269194 | 2004-09-06 03:38:00 | A simple macro will readily perform this task. Enter your value in E5 then run the macro. Sub FIND_VALUE() Dim rng As Range Set rng = Range("h2:o20") Range("e5").Select For Each cell In rng If cell.Value = Range("e5") Then cell.End(xlDown).Select MsgBox "Bottom value in column = " & ActiveCell.Value End If Next If ActiveCell = Range("e5") Then MsgBox "No Match Found" End If End Sub This simple macro has checking if there is no match, but assumes that data is continuous in each column, and the bottom column entry will not match the required value. All this can be worked around if necessary. |
Russell D (18) | ||
| 269195 | 2004-09-06 11:48:00 | Thanks for that. I would like a formula though which would place the result directly into a cell. Anyone have any other suggestions? |
bpt1 (419) | ||
| 269196 | 2004-09-06 23:14:00 | Replace MsgBox "Bottom value in column = " & ActiveCell.Value with Range("yourcell") = activecell.value "yourcell" is the cell where you want the result to appear. I too would like to see the formula which anyone else can provide which would give what you want. |
Russell D (18) | ||
| 269197 | 2004-09-07 03:54:00 | > Thanks for that. I would like a formula though which > would place the result directly into a cell. > Anyone have any other suggestions? Hi, I dont know the answer sorry but I suspect it will involve using a combination of Index & Match with an array. I could create a custom function for you using code but Im sure someone at Mr Excel (www.mrexcel.com) such as Aladdin or Fairwinds will have a solution if you make a post there. I would be interested to know the result though so when you do get a solution please post back here. regards parry. |
parry (27) | ||
| 1 2 | |||||