Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup table value
I posted this in the Excel Workbook section, but I think it might nee
to be addressed in here as it appears to be more of a programmin question, than an excel questoin. Anyway here goes I have a formula that returns a value in sheet1. Then I have a tabl full of values, in the same workbook different sheet, that correspond to that formula. I need to look up the value returned in sheet1 an display the appropriate column/row where that value is located. Example: value returned is 3.175 The table goes form 21 to 65 on both columns and rows. 3.175 is located at 37 column and 54 row, which is also cell locatio R35. I need it to return the appropriate gears where B2:AT2 is Gear and A2:A46 is Gear A. Is it possible to do this? If I can do the above, then I will work on the next part below, but i it is possible to do both at the same time that would be even better. So for the next part, the values in the table are not exactly sa 3.175, but might be 3.140 or 3.1764. So I guess I need to find a wa to minimize the difference between all of them and take the cell wit the smallest difference. I might be getting in over my head, but it will make things a LO easier if I can figure it out -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup table value
I developed a formula to do such a search with multi criteria, the cod
is below for you to study. Hopefully this helps and gets you closer t what you want. (note this formula is consumes a bit recalculating tim when copied over & over) =IF(ISNA(INDEX(sheet1!$B:$B,MATCH(CONCATENATE($B13 8,C$136),sheet1!$A:$A,0)))=TRUE,0,INDEX(sheet1!$B: $B,MATCH(CONCATENATE($B138,C$136),sheet1!$A:$A,0)) ) -Fabl -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup table value
I developed a formula to do such a search with multi criteria, th
code is below for you to study. Hopefully this helps and gets yo closer to what you want. (note this formula is consumes a bi recalculating time when copied over & over) =IF(ISNA(INDEX(sheet1!$B:$B,MATCH(CONCATENATE($B13 8,C$136),sheet1!$A:$A,0)))=TRUE,0,INDEX(sheet1!$B: $B,MATCH(CONCATENATE($B138,C$136),sheet1!$A:$A,0)) ) -Fable It looks like that can get me started, but it is always returning 0. went in and changed the return value to 1 and that's what came up. So was wondering what CONCATENATE($B13 8,C$136),sheet1!$A:$A,0) was referring to. I'm assuming on your shee you are using b138 and c136, where I am not, but I don't know what t change that to for starters. Thanks Fabl -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup table value
Hii Vonner,
Extra info I left out, place a False after 0(zero), why? your query ma not be in desending order therefore returing 0(zero). =INDEX(sheet1!$B:$B,MATCH(CONCATENATE$B138,C$136), sheet1!$A:$A,0,FALSE the reason why I used a CONCATENATE formula is the creative part o this formula, I had too merge the criteria in order to search th information I wanted since LOOKUP / INDEX only allows 1(one) looku item. In my case it was date and market segment I merge/CONCATENATE, i looks like this 09/15/04CORP since this value was unique in the databas it allowed me to "lookup" and return row "2" (3,4,5 etc) which contain the information I wanted. Hopefully this helps! Fabl -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup table value
I have a similar problem, but I can't seem to get it to work. Could it be
because I'm on Excel 2000? My table is something like Jan Feb Mar Jones 1 2 3 Smith 4 5 6 Murphy 7 8 9 My statement is: intMonthlyInitiativeHours = Index(A1:D4, Match("Smith", A1:D4, 0), Match("Feb", A1:D4, 0)) I get Compile Error: Expected: list seperator or ) and it points to the first colon. Can you please help? Many Thanks. "Fable " wrote: I developed a formula to do such a search with multi criteria, the code is below for you to study. Hopefully this helps and gets you closer to what you want. (note this formula is consumes a bit recalculating time when copied over & over) =IF(ISNA(INDEX(sheet1!$B:$B,MATCH(CONCATENATE($B13 8,C$136),sheet1!$A:$A,0)))=TRUE,0,INDEX(sheet1!$B: $B,MATCH(CONCATENATE($B138,C$136),sheet1!$A:$A,0)) ) -Fable --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
table lookup | Excel Worksheet Functions | |||
Table lookup | Excel Discussion (Misc queries) | |||
Lookup data in a variable table & retrieve data from a pivot table | Excel Worksheet Functions | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions |