Confusion with INDEX & MATCH

  • Thread starter Thread starter thomsonpa
  • Start date Start date
T

thomsonpa

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
 
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.
 
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.
 
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
 
Back
Top