Index Match is pulling from different row than intended

Ezybrrzy

New Member
Joined
Sep 17, 2019
Messages
5
I am trying to index a date from another worksheet with the formula

=TEXT(INDEX('JAN-APR INV'!$A:$I,MATCH(YTD!A247,YTD!A:A,0),6),"mm/dd/yy")

I want the formula to pull date based on the ID associated which is column A. The date is in worksheet "Jan-Apr Inv" row 6. It will pull the date from A247, but doesnt line up with ID on YTD worksheet.

I don't know if index match is the right formula or if another formula is needed in this situation.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi Ezybrrzy,

The INDEX has following parameters:
- Lookup_Range - Array
- Row_Num
- Column_Num

You are writing that the date is in row 6, however, in your formula 6 is referenced as column_number, while MATCH formula is for row_number. Can you please check if this might be the problem, if not, can you provide a bit more information how the data looks like?

Br
pella88
 
Upvote 0
Hi & welcome to MrExcel.
Should this
MATCH(YTD!A247,YTD!A:A,0),6)
be
MATCH(YTD!A247,'JAN-APR INV'!A:A,0),6)
 
Upvote 0
Sorry, yes it should be "column 6" not "row 6"

The MATCH(YTD!A247,'JAN-APR INV'!A:A,0),6) should be MATCH(YTD!A247,'JAN-APR INV'!B:B,0),6), as the ID on that worksheet is in column B.

The formula works in a sense it pulls the information in row 247 from worksheet "Jan-APR INV" into row 247 on the "YTD" worksheet. It disregards matching ID # from the "YTD" worksheet that i want it to pull from. The date in row 247 in the "JAN-APR INV" worksheet would be 1/19/19, but the actual date i want is 4/18/19 from row 2417.
 
Upvote 0
Does the ID number exist multiple times on the Jan-Apr sheet, or only once?
 
Upvote 0
In that case your formula will only ever pick-up the first occurrence.
I would suggest you post some sample data along with an accurate description of what you are trying do.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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