MATCH/INDEX (MAYBE LOOKUP)

Shlby6

New Member
Joined
May 9, 2006
Messages
36
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!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

Well-known Member
Joined
May 19, 2002
Messages
8,458
Book2
ABCD
1Eff AgeNULDep %
21015#N/A
321411
41615#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

Well-known Member
Joined
Jul 12, 2006
Messages
1,141

ADVERTISEMENT

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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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! :)
 

Forum statistics

Threads
1,141,627
Messages
5,707,495
Members
421,511
Latest member
mgroah1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top