Index Match with lots of duplicate lookup value and duplicate array value!!!

[vivroy]

Hello,

I am trying to do index match and get next match item instead of first match for all entries.

As you can see above first First work sheet is wf and second is vs. I am trying to get next matched invoice #.

As you can see I have Amt 21 eleven times. however invoice for Amt 21 is only nine. For remaining two its ok if I get error(N/A) and Vise versa is fine too. Like I have more invoice for same amount than amount I am looking for.

Thank you for your help.
Vivroy

 

[Chrisdontm]

It looks like you copied the amounts from the second sheet over to the first sheet and sorted them smallest to largest...

You could make column "E" on the "vs" sheet copy column "A"...(If you don't want anyone to see that then make the width of column "E" under 1)

Then when you copy over column "D" onto the other sheet, copy columns " D & E " instead, and paste as values only,

Highlight both columns and sort both, you will get what you are shooting for I believe...

 

[vivroy]

It looks like you copied the amounts from the second sheet over to the first sheet and sorted them smallest to largest...

You could make column "E" on the "vs" sheet copy column "A"...(If you don't want anyone to see that then make the width of column "E" under 1)

Then when you copy over column "D" onto the other sheet, copy columns " D & E " instead, and paste as values only,

Highlight both columns and sort both, you will get what you are shooting for I believe...

Thank you but that is not what I am looking for.

If there is a way index match would look for next available match if first one is used and look for third match if first and second is used and so on......

 

[vivroy]

I have been searching, and found it would work if I would created unique value. However I have to create unique value for amount on both working sheet. Is there any other way?