# MATCH/INDEX (MAYBE LOOKUP)

#### Shlby6

##### New Member
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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))

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))

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))

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

NBVC:

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?

Cheers!

Sean

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

NBVC:

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?

Cheers!

Sean

You got it!

Thanks to all!!!

It worked, thanks again to all who replied.

Thanks NBVC

Replies
3
Views
169
Replies
4
Views
285
Replies
20
Views
389
Replies
3
Views
225
Replies
10
Views
271

1,219,570
Messages
6,149,044
Members
450,853
Latest member
xtiinctt

### 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.

### Which adblocker are you using?

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

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