Forum Home
Press F1
Thread ID: 48898	2004-09-04 06:22:00	excel formula	bpt1 (419)	Press F1

Post ID	Timestamp	Content	User
269179	2004-09-04 06:22:00	I am looking for an excel formula which selects a cell from a table of data according to the following criteria: if cell B2 is equivalent to a cell in column C, then if A2<D1, select the cell in in column D (on the same row as the equivalent cell = 13-Apr), otherwise if D1<A2<E1, select the cell in in column E (on the same row as the equivalent cell) etc. A B C D E 1 1583 1700 2 1500 0.263 0.000 5-Apr 12-Apr 3 0.053 25-Mar 1-Apr 4 0.263 13-Apr 21-Mar Anyone help? Thanks	bpt1 (419)
269180	2004-09-04 07:22:00	I'm not sure that I follow what you're asking here - any chance you could explain it a little differently? Cheers, Mike.	Mike (15)
269181	2004-09-04 08:33:00	Ok, I am ultra confused but think I know what you are looking for but I'm going to make some massive assumptions here. Assumptions : 1. The table of data is a perfectly rectangular or square in shape and is complete. 2. The table of data starts at D2 and extends (for the purpose of this example) to cell H9. The vertical lookup values are contained in the cells C2 to C9 and the horizontal lookup values are contained in cells D1 to H1. 3. The vertical lookup values in column C are possibly in random order but the horizontal lookup values are in ascending order from left to right. 4. You wish to find a cell within the data table by finding an exact match for the value in B2 to the values in column C (C2 to C9) and by finding an approximate match for the value in A1 to the values in the first row (from D1 to H1). For the value in A1, where an exact match is not found in the first row then the next highest value is selected. 5. When you say the formula "selects" a cell, I have assumed that you wish to return its value and for the purposes of this example, I have assumed you are entering the formula into cell A3. 6. That if you enter an invalid value into either A2 or B2 then an error message is returned (standard Excel error or an invented one). 7. That the world is in fact round the earth circles the Sun :) So essentially you want to use a combination of a vertical and horizontal lookup functions. But I don't think you can use both functions together (guys, please correct me if I am wrong) so you will need some conditional if statements inside a lookup function, as follows : =VLOOKUP(B2,C2:H9,IF(A2<=D1,2,IF(A2<=E1,3,IF(A2<=F1,4,IF(A2<=G1,5,IF(A2<=H1,6,"error"))))),FALSE) Clumsy I know bit it works. The values in the formula will differ to yours because my table of data only goes from D2 to H9 - yours will be a lot bigger. Where you have many many columns of data then this formula may not work for lack of space to enter it all in. How many columns of data are there? Please be aware that to add more rows and columns of data then to change my formula you will need to repeat the bit IF(A2<*1,?, immediately before "error" and to add another ) immediately after "error" - where * is the column label (the next one is I) and ? is the next column number (the next one is 7). Also, the very beginning bit of the formula will be different (i.e. =VLOOKUP(B2,C2:@@ where @@ is the last record at the bottom right of your table of data - in my example it was H9). Please advise if I am way off track or if some slight tweaking is needed. HTH, Andrew	andrew93 (249)
269182	2004-09-04 08:37:00	Thought that might happen with PF1 formatting. Where my previous post shows this in the formula (whre the line wraps over to the next one) : .... IF(A2&l t;=G1 .... it should actually be : .... IF(A2<=G1 ....	andrew93 (249)
269183	2004-09-04 12:57:00	I see that my table did not appear properly and so it must have been a bit confusing. My actual spreadsheet is as follows: I have a table of data H2 to o20. I want to return the value of a cell within that table. The column g2:g20 contains data which need to match my input data in cell d2. The header cells of may table h1,j1,i1 etc contain data which need to relate to my input data in cell a2. If the number in cell d2 matches a number in column g AND if the number in cell a2 is less than the number in cell h1, then return the value from the cell h# which is on the same row as the matching number in column g, if the number in cell a2 is greater than the number in cell h1 but less than the number in cell i1, then return the value from the cell i# which is on the same row as the matching number in column g, etc. Is that any better?	bpt1 (419)
269184	2004-09-04 13:26:00	try this : =VLOOKUP(D2,G2:O20,IF(A2<=H1,2,IF(A2<=I1,3, IF(A2<=J1,4,IF(A2<=K1,5,IF(A2<=L1,6, IF(A2<=M1,7,IF(A2<=N1,8,9))))))),FALSE) I put some carriage returns into the formula so that PF1 doesn't muck it up. HTH, A	andrew93 (249)
269185	2004-09-04 13:28:00	D'Oh! ... and it did muck it up .... the funny bit should look like this : = V L O O K U P ( D 2 , G 2 : O 2 0 , but without the spaces	andrew93 (249)
269186	2004-09-04 23:38:00	this site might interest you mrexcell.com	kirrie kiwi (279)
269187	2004-09-04 23:40:00	excuse my spelling site is www.mrexcel.com	kirrie kiwi (279)
269188	2004-09-04 23:45:00	Thanks , that worked. But I've got one more. This time I want to find a value in a table which matches the value in cell e5, and then returns a value from the bottom row of the same column (in the table) in which the matching value is found. Know any other helpful resources where I can get information on these excel formulae (and I don't mean Microsoft)? Thanks	bpt1 (419)
1 2