I have a list of numbers on sheet 1 and need to add a code in the next column.
On sheet 2 the first column has the codes, and the next 30 columns have
numbers.
I created named ranges for the first column as "mealtable" and the next 30
columns as "lookupvalueh".
I tried the following formula without success:
=INDEX(MEALTABLE;MAX(ROW(MEALTABLE)*(B20=LOOKUPVALUEH))

B20 is the value I want to look for in the table, bringing back the code
from the first column of the row with the value in.

any help in putting me on the right track would be appreciated. As you can
see I am not an expert, but work on trial and error. I did press control
shift & enter to put the formula in brackets.

Hi,
Not very clear what you need, you want to look for a table and bring a code
made of 30 columns numbers ??, could you give an example thanks

"thomsonpa" wrote:

> I have a list of numbers on sheet 1 and need to add a code in the next column.
> On sheet 2 the first column has the codes, and the next 30 columns have
> numbers.
> I created named ranges for the first column as "mealtable" and the next 30
> columns as "lookupvalueh".
> I tried the following formula without success:
> =INDEX(MEALTABLE;MAX(ROW(MEALTABLE)*(B20=LOOKUPVALUEH))
>
> B20 is the value I want to look for in the table, bringing back the code
> from the first column of the row with the value in.
>
> any help in putting me on the right track would be appreciated. As you can
> see I am not an expert, but work on trial and error. I did press control
> shift & enter to put the formula in brackets.

>I tried the following formula without success

Can you be more specific as to what that means? Did you get an error? Did
you get a result but it was not the result you expected?

How about telling us what the actual range addresses are for MEALTABLE and
LOOKUPVALUEH.

--
Biff
Microsoft Excel MVP


"thomsonpa" <(E-Mail Removed)> wrote in message
news:590EEC5B-247E-4C84-B943-(E-Mail Removed)...
>I have a list of numbers on sheet 1 and need to add a code in the next
>column.
> On sheet 2 the first column has the codes, and the next 30 columns have
> numbers.
> I created named ranges for the first column as "mealtable" and the next 30
> columns as "lookupvalueh".
> I tried the following formula without success:
> =INDEX(MEALTABLE;MAX(ROW(MEALTABLE)*(B20=LOOKUPVALUEH))
>
> B20 is the value I want to look for in the table, bringing back the code
> from the first column of the row with the value in.
>
> any help in putting me on the right track would be appreciated. As you can
> see I am not an expert, but work on trial and error. I did press control
> shift & enter to put the formula in brackets.

Sorry, I did not ask for notification of reply, and thought nobody had replied.

below is an example of part of the table.

FEA FB DCC S LH3 LH3 SIN
FEA FM DCC LH3 LH3 MNL
FEA FM DCC S LH3 LH3
FEA FB S LH4 LH4 BKK
FEA FM S LH4 LH4 DME MSQ
EUR MH FB MH1 MH1 ALA IST ATH
EUR MH FM MH1 MH1 TSE

I want to look up a value (ie: IST) and bring back the result from the first
column (ie EUR MH FB MH!) from the matching row. Is this possible? I thought
it would be easier to use named ranges so I could expand the range easier
than changing all the formulas for Match.


"Eduardo" wrote:

> Hi,
> Not very clear what you need, you want to look for a table and bring a code
> made of 30 columns numbers ??, could you give an example thanks
>
> "thomsonpa" wrote:
>
> > I have a list of numbers on sheet 1 and need to add a code in the next column.
> > On sheet 2 the first column has the codes, and the next 30 columns have
> > numbers.
> > I created named ranges for the first column as "mealtable" and the next 30
> > columns as "lookupvalueh".
> > I tried the following formula without success:
> > =INDEX(MEALTABLE;MAX(ROW(MEALTABLE)*(B20=LOOKUPVALUEH))
> >
> > B20 is the value I want to look for in the table, bringing back the code
> > from the first column of the row with the value in.
> >
> > any help in putting me on the right track would be appreciated. As you can
> > see I am not an expert, but work on trial and error. I did press control
> > shift & enter to put the formula in brackets.

Assuming lst is in col G and j1 is the cell to match
col a
=INDEX(A:A,MATCH(J1,G:G,0))
col a & b
=INDEX(A:A,MATCH(J1,G:G,0)) & " " & INDEX(B:B,MATCH(J1,G:G,0))

a macro to make a string
Sub getdata()
mr = Application.Match(Range("j1"), Columns("G"), 0)
'MsgBox mr
For i = 1 To 4
msg = msg & Cells(mr, i) & " "
Next i
MsgBox msg
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"thomsonpa" <(E-Mail Removed)> wrote in message
news:50F01FE6-D229-4901-91D5-(E-Mail Removed)...
> Sorry, I did not ask for notification of reply, and thought nobody had
> replied.
>
> below is an example of part of the table.
>
> FEA FB DCC S LH3 LH3 SIN
> FEA FM DCC LH3 LH3 MNL
> FEA FM DCC S LH3 LH3
> FEA FB S LH4 LH4 BKK
> FEA FM S LH4 LH4 DME MSQ
> EUR MH FB MH1 MH1 ALA IST ATH
> EUR MH FM MH1 MH1 TSE
>
> I want to look up a value (ie: IST) and bring back the result from the
> first
> column (ie EUR MH FB MH!) from the matching row. Is this possible? I
> thought
> it would be easier to use named ranges so I could expand the range easier
> than changing all the formulas for Match.
>
>
> "Eduardo" wrote:
>
>> Hi,
>> Not very clear what you need, you want to look for a table and bring a
>> code
>> made of 30 columns numbers ??, could you give an example thanks
>>
>> "thomsonpa" wrote:
>>
>> > I have a list of numbers on sheet 1 and need to add a code in the next
>> > column.
>> > On sheet 2 the first column has the codes, and the next 30 columns have
>> > numbers.
>> > I created named ranges for the first column as "mealtable" and the next
>> > 30
>> > columns as "lookupvalueh".
>> > I tried the following formula without success:
>> > =INDEX(MEALTABLE;MAX(ROW(MEALTABLE)*(B20=LOOKUPVALUEH))
>> >
>> > B20 is the value I want to look for in the table, bringing back the
>> > code
>> > from the first column of the row with the value in.
>> >
>> > any help in putting me on the right track would be appreciated. As you
>> > can
>> > see I am not an expert, but work on trial and error. I did press
>> > control
>> > shift & enter to put the formula in brackets.