Index formula lookup

sprtfan34

New Member
Joined
Oct 23, 2019
Messages
19
I am using the following formula:
=INDEX('120219'!$Z$13:$Z$112,MATCH($A2,'120219'!$B$13:$B$112,0))

However is the value in A2 does not exist in the B column of my table then it returns a random value and throws other values off as well. if there a work around?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
With that formula, if A2 is not found, it should return #N/A, not a "random value"
 
Upvote 0
It actually entered the value under it in the column. Almost as if the returned value was shifted up by one cell. Which threw all other returned values off by one as well.
 
Upvote 0
If the formula in post#1 is exactly the same as the formula in your sheet, I don't think that can happen.

If A2 is not found in col B you will get #N/A, if it is found you will get the corresponding value from col Z. So if its found in B35, it will return Z35.
 
Upvote 0
The lists may not be in the same order from the master page to the "120219" page will this pose a problem. I thought it would just search for the name and return the value no matter where it was in the table.
 
Upvote 0
The order makes no difference.
Did you copy/paste your formula to the board, or type it out again?
 
Upvote 0
In that case it should work.
Do you have any merged cells in either of your sheets?
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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
Back
Top