CODEWITHSUNDEEP
excelexcel-formula
Spectraljump
Spectraljump

Reputation: 4667

Find value in another table, return row index

I'm inexperienced in Excel functions and need some help with the following scenario:

Table I has 2 columns:

X      Y 
3.2   result
4.7   result
1.2   result

Table II has these columns:

A    B    C    D
1   1.2  0.0  2.3
2   4.1  3.2  0.0
3   0.0  4.7  0.0

I will try to illustrate what I want to do in C++/C#/Java programming syntax:

In Y[1] I want to store:

foreach (int value in TableII)   //look for X[1] in Table 2
   if( value == X[1] ){          //when you find it, 
      return A[value.rowNumber]; //return the corresponding value from A
      //break;
   }

In other words, I want to find X[1] in table II (it will always be there, and always just once), and return the corresponding value from column A[of the same index as the match].

I've looked at HLOOKUP but there's more to it that just one command. Please help.

[EDIT]

I've tried this =INDEX(Column1,IFERROR(MATCH(I2,Column2,0),IFERROR(MATCH(I2,Column3,0),IFERROR(MATCH(I2,Column4,0),IFERROR(MATCH(I2,Column5,0),IFERROR(MATCH(I2,Column6,0),IFERROR(MATCH(I2,Column7,0),IFERROR(MATCH(I2,Column8,0),MATCH(I2,Column9,0)))))))))

But I get the "more levels of nesting than are allowed in the current file format" error. If I remove just one IFERROR(MATCH(I2,Column8,0), then it works. But I need Columns 2 to 9, with Column1 holding the index.

I thought Excel 2007 and 2010 do not have nesting limits any more. What gives?

(For anyone new to Excel reading this, "Column1" etc. are made by selecting the column, right clicking on the selection, and "Define Name".)

[EDIT2]

So I "solved" my nesting problem by dividing the formula into 2 parts, with a +, having the ifs return 0 in case there's no match in either side. This way you either have something + 0 or 0 + something, so it works.

Here's the formula I'm using right now:

    =INDEX(Column1, IFERROR(MATCH(I3,Column2,0),IFERROR(MATCH(I3,Column3,0),IFERROR(MATCH(I3,Column4,0),IFERROR(MATCH(I3,Column5,0),IFERROR(MATCH(I3,Column6,0),IFERROR(MATCH(I3,Column7,0),0))))))    
                    +     
                    IFERROR(MATCH(I3,Column8,0),IFERROR(MATCH(I3,Column9,0),0))
          )

I've accepted Chris' answer, as he did most of the work, but I would still like to know if my workaround is good practice and if there isn't a shorter/better way to do this.

Thank you.

Upvotes: 1

Views: 5615

Answers (2)

nutsch
nutsch

Reputation: 5962

If your values are unique, you can use the following formula without having to do any nesting:

=INDEX(column1,SUMPRODUCT((Table2=G24)*ROW(Table2)))

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53166

Here's a possible formula for Y (assumes table I is located in columns G:H)

=INDEX($A:$A,IFERROR(MATCH(G2,$B:$B,0),IFERROR(MATCH(G2,$C:$C,0),MATCH(G2,$D:$D,0))))

Note the IsError function was introduced in Ecel 2007

If your tables are actually defined as excel tables you can use

=INDEX(Table2[Column1],IFERROR(MATCH([@X],Table2[Column2],0),IFERROR(MATCH([@X],Table2[Column3],0),MATCH([@X],Table2[Column4],0))))

(substitute your own table and column names)

Upvotes: 1

Related Questions