Problem with Index and Match

Morning, Gurus. I've used this formula (garnered from the geniuses on this site) to vlookup data from one sheet to another if 2 criteria were met, and it worked in that instance. Now I am trying to edit the formula to work in a different workbook, with different columns, and seem to be having trouble with the edits.

I have two worksheets with the same column headings, but the data may be different. I am trying to compare the price in one sheet, (Key), with the price in the sheet where I am entering the formula, (CanKey). I have attached a sample of the workbook, after removing sensitive data. Here is the formula I am entering in column J2 of my CanKey worksheet to return the cost from the Key worksheet (Column I) if the vendor number (Column G) and the product number (Column A) match:

Please Login or Register  to view this content.

Not sure what I am doing wrong. Any help will be greatly appreciated.

Have a good one.

Try this Array Formula. They're a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

However, it will return only blanks, as there are NO matching values in your example.

=IFERROR(INDEX(Key!$I$1:$I$10,MATCH(A2&G2,Key!$A$1:$A$10&Key!$G$1:$G$10,0)),"")

There are NO matches found in column G. If you make correction you can use this non-array version

=IFERROR(INDEX(Key!$I$2:$I$10,MATCH(A2&G2,INDEX(Key!$A$2:$A$10&Key!$G$2:$G$10,0),0)),"")

Originally Posted by hutch@edge.net

Hard to believe how slow it was to calculate though, since my full data was over 77,000 lines. One I ran the formula down the whole page it took about 10 minutes to calculate for all the rows. Must be the index portion that makes it so slow.

Range concatenation is very inefficient.

Range1&Range2 =