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

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

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

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

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/