Index/Match issues when sorting

bamf3000

New Member
Joined
Dec 13, 2013
Messages
33
Hey Everyone,

I am trying to lookup criteria to the left of my usual vlookup, so thought I would give Index/Match a look. The formula only seems to return the reference cells in numeric order instead of looking at the unique item number I want it to use.

Here is the formula I have been using:

=INDEX('LW Dump'!$A$5:$A$500,MATCH('01.03.14 Fcst Export'!$B2,'01.03.14 Fcst Export'!$B$2:$B$500,0))

A5:A500 are what I want returned, but I want it to return the cell that corresponds with B2

Thanks!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,790
Office Version
  1. 365
Platform
  1. MacOS
so you are looking at sheet
'01.03.14 Fcst Export'
cell B2
and comparing that value to
01.03.14 Fcst Export
cell B2 to B500
to itself

is that what you want to do ?



 

bamf3000

New Member
Joined
Dec 13, 2013
Messages
33
I would like the formula to look for the data in cell B2 and cell B2 to B500 is the table that it should be looking for. If that makes sense. I want it to reference that data in B2 and then pull the data that is in LW Dump in Column A
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,790
Office Version
  1. 365
Platform
  1. MacOS
isnt that just going to return the value in A2 for match in B2
and A3 for match on B3
etc
 

bamf3000

New Member
Joined
Dec 13, 2013
Messages
33

ADVERTISEMENT

Maybe?? And that seems to be what it is doing, but Im not sure how to change it. I would like like it to lookup what is in cell B2, and then give the corresponding data in column A that is associated with B2, so on and so forth for all of Column B
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,790
Office Version
  1. 365
Platform
  1. MacOS
it is doing that
as its looking up itself

you may need to describe more detail what you need - perhaps with an example

at the moment

if you lookup the value in B2 from the same list of B2-B500 - then that will simply be B2
then to bring back the corresponding value in A2-A500 - will again now be A2 as the results was in B2
 

fireproof101

New Member
Joined
Jan 5, 2014
Messages
5

ADVERTISEMENT

Hey Everyone,

I am trying to lookup criteria to the left of my usual vlookup, so thought I would give Index/Match a look. The formula only seems to return the reference cells in numeric order instead of looking at the unique item number I want it to use.

Here is the formula I have been using:

=INDEX('LW Dump'!$A$5:$A$500,MATCH('01.03.14 Fcst Export'!$B2,'01.03.14 Fcst Export'!$B$2:$B$500,0))

A5:A500 are what I want returned, but I want it to return the cell that corresponds with B2

Thanks!


It would help to know what columns A and B are in each sheet. Remember, the Match function within the Index function is used to find a row reference. As you have it written now, your function is going to find B2 in '01.03.14 Fcst Export'! and give you the corresponding row number in the same sheet within the range B2:B500 (which I can tell you right now is 1). The Index will take that row number (1 in this case), and give you the corresponding value in A5:A500 of sheet 'LW Dump'!. Perhaps you meant to write it like this (the change I made is underlined)?

=INDEX('LW Dump'!$A$5:$A$500,MATCH('01.03.14 Fcst Export'!$B2,'LW Dump'!$B$2:$B$500,0))

What this will do is look for whatever is in B2 of
'01.03.14 Fcst Export'! within the range of B2:B500 in 'LW Dump'!, and that corresponding row number will be used in the INDEX function for the range of A5:A500 in 'LW Dump'!. Hope that helps!
 

bamf3000

New Member
Joined
Dec 13, 2013
Messages
33





Ok, so I would like to have my formula reference the SKU # in the first sheet, and then pull in the SC associated with that SKU # from the 2nd sheet,

So that when it looks up Product 14, it says ok, this should be SSSQ.

Hopefully that helps and thanks for your help!
 

fireproof101

New Member
Joined
Jan 5, 2014
Messages
5
I'm guessing the top sheet is '01.03.14 Fcst Export'! and the bottom is 'LW Dump'!? If that's the case, try this formula:

=INDEX('LW Dump'!$B$5:$B$500,MATCH('01.03.14 Fcst Export'!$B2,'LW Dump'!$C$2:$C$500,0))
 

bamf3000

New Member
Joined
Dec 13, 2013
Messages
33
Thats it!!! Thank you guys for your help!! After looking at my formula, I can see where my mistake was and how it was referencing the wrong column. Thanks again!
 

Forum statistics

Threads
1,136,354
Messages
5,675,294
Members
419,559
Latest member
BraytonM

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