I would like find a formula that would report the value in column 1 of a table when looking in a table of numbers.

For example

gear low med high
a1 75 27 63
a2 55 26 52
a3 1 2 3
b1 4 7 11
b2 101 25 3
b3 85 28 3

I would like a formula to look up the value 85 in the array and report the gear settings for the value 85.

Dear mdawn, Good Evening.

It´s not dificult to do what you need.

But your explanation is a little confuse.

You have 4 columns.

a) In which column do you desire to search the value?

b) Do you need as an answer all the other three columns?


After your answer I believe that the solution will be ok.
I think it´s possible to use MATCH Function and/or Vlookup Function.

I´m Wainting for your answer

Mazzaropi
-------------------------------------------------------------------------

I need the formula look in all of the columns at one time and I want it to report back the column heading of the column that contains the number I am searching for as well as the row title. For example if I want to find the number 85. I would like excel to give me the gear settings "low b3". This does not have to be in one formula but can be in two. Low=column heading b3=row heading.
Thanks.

----------------------------------------------------------------------

Dear mdawn, Good Evening.

I did an example for you.
It´s here : 16-02-2011_ExcelBanter_Search_for_MDAWN_V1-0.xls

ATTENTION:
As you are looking for a specific number, if at a table there are repetitive numbers your answer will be the first gear setting number that the formula finds.
The search occurs left to right and top to down.

I used Conditional Formatting to show the value at a table.
Just as a appearance complement.

Look at the number 3 at the example.


Tell me if it worked for you.

It works well. Thanks so much for your help!