• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index and Match question

N

nico

New Member
Hi,
I', struggling to find the right sequence for my index and match formula.

What I have is a table that I import from an XML file with headers (call it table 1 for now)

On another sheet I have a list with article numbers (which are also in table1, column1) and I need to add a value in column 2 that is at the crossing of the row with name in cel A1 en the column with name "x".

Some examples:
Table1
r1 column1 column2 column3
r2 art1 123 abc
r3 art2 456 def

Other sheet should produce this output
r1 article description
r2 art2 def

Normally I would do this with a normal lookup function and return the value for the 3 column. My issue is that the position of the column is constantly changing and Therefor I cannot rely on this value. So I was thinking that if I could return the value of the column with "name column3"(table1) for the row that has in column1 the value of art2 than that would solve my problem.

I've been trying the index and match but I think I did something wrong with one of the values.

Any tips would be most welcome.

Thnx,
Nico
 
Hi there,

Lets say your raw data is in a Table (called "Table1") and on the other sheet you want to lookup the value in A2, then you can use this:
Code: 
=INDEX(Table1[#All],MATCH(A2,Table1[Column1],),MATCH("Name of column",Table1[#Headers],))
 
Or a non-Table version of the formula:
Code: 
=INDEX(Data!$A$2:$G$12,MATCH(A2,Data!$A2:$A12,),MATCH("Name of column",Data!A$1:G$1,))
 
Hi Xiq,

Thanks you for the fast reply !
I adapted the formula to my situation and it works with 1 small thing to solve.
As a value I get the value of the cell just above the right value. Does this have to do with the headers ?

Nico
 
Hi Nico,

Try replacing Table1[#All] with Table1 in the beginning of the formula. You will then refer to the whole table excluding the headers, and it might solve your problem.

Regards,
 
You must log in or register to reply here.
Back
Top