MATCH/INDEX (MAYBE LOOKUP)

[Shlby6]

I am trying to figure out how to look up a # from a table and insert it into a spreadsheet. Not sure how to do, maybe someone could help. See the following example:

On sheet one I have the following:

Eff Age____NUL_____Dep %
10________ 15____what i need
2 _________14____what i need
16________ 15____what i need

On sheet two I have the table that has the Eff Age in Column A, the NUL in Row 2, and the Dep % inside the table, kind of like this.

Eff Age____15___14___13 (NUL)
1__________5___ 5____6
2__________10__11___12
3__________15__16___18
4__________21__23___25
and so on

So for example row 3 on sheet 1 (2, 14, what i need), would ultimately coorespond to row 3, column 3 (11) on sheet 2.

Is there a formula I can use to have this calculated based on columns 1 and 2 on sheet 1.

Any help would be greatly appreciated. Thanks!

 

[Aladin Akyurek]

Remove the (NUL) bit from Sheet2...

Then invoke in C2 on Sheet1...

=INDEX(Sheet2!$B$2:$D$5,MATCH(A2,Sheet2!$A$2:$A$5,1),MATCH(B2,Sheet2!$B$1:$D$1,-1))

 

[NBVC]

Use Index/Match

Like so:

=Index(Sheet2!$B$3:$D$50,Match(A2,Sheet2!$A$3:$A$50,0),Match(B2,Sheet2!$B$2:$D$2,0))

you may have to adjust ranges to suit (and sheet names too))

 

[texasalynn]

Book2
	A	B	C	D
1	Eff Age	NUL	Dep %
2	10	15	#N/A
3	2	14	11
4	16	15	#N/A
Sheet2

formula in c2
=INDEX(Sheet1!$B$2:$D$5,MATCH(A2,Sheet1!$A$2:$A$5,0),MATCH(B2,Sheet1!$B$1:$D$1,0))

 

[seenfresh]

Use Index/Match

Like so:

=Index(Sheet2!$B$3:$D$50,Match(A2,Sheet2!$A$3:$A$50,0),Match(B2,Sheet2!$B$2:$D$2,0))

you may have to adjust ranges to suit (and sheet names too))

I am a relatively new Rookie and would like to share my understanding of your formula: Please comment if I am on the right track

Would appreciate your advice in my understanding!

NBVC:

Your formula uses this theory:

INDEX(array,row_num,column_num)

Thus Sheet2!$B$3:$D$50=array, Match(A2,Sheet2!$A$3:$A$50,0)=row#"", Match(B2,Sheet2!$B$2:$D$2,0))=Column#""

The result would provide a cell value/location in R1C1 reference within the array (like an intersection point between R"" & C"" within the array range)

Is this the basic function?

Thanks in advance

Cheers!

Sean

 

[NBVC]

Use Index/Match

Like so:

=Index(Sheet2!$B$3:$D$50,Match(A2,Sheet2!$A$3:$A$50,0),Match(B2,Sheet2!$B$2:$D$2,0))

you may have to adjust ranges to suit (and sheet names too))

I am a relatively new Rookie and would like to share my understanding of your formula: Please comment if I am on the right track

Would appreciate your advice in my understanding!

NBVC:

Your formula uses this theory:

INDEX(array,row_num,column_num)

Thus Sheet2!$B$3:$D$50=array, Match(A2,Sheet2!$A$3:$A$50,0)=row#"", Match(B2,Sheet2!$B$2:$D$2,0))=Column#""

The result would provide a cell value/location in R1C1 reference within the array (like an intersection point between R"" & C"" within the array range)

Is this the basic function?

Thanks in advance

Cheers!

Sean

You got it!

 

[Shlby6]

Thanks to all!!!

It worked, thanks again to all who replied.

 

[seenfresh]

Thanks NBVC