Lookup and return multiple values

**kashaikh78** · 12-04-2019, 03:37 PM

Hi Gurus,

I am trying to populate values in sheet 2 column A to Column B in sheet 1. The data in lookup field is not unique therefore can't populate the correct customer number from sheet 2 to sheet 1. The lookup field in this case is OBA number.

Since the data consist of thousands of lines, excel usually crashes if I try to apply any formula. Is it possible to have a macro to resolve this issue?

I attach a sample file for reference.

**Sam Capricci** · 12-04-2019, 03:58 PM

I know you want VBA but maybe you ought to reload the sample because using a simple index/match formula against the OBA number in sheet1 column A to try to match against the OBA number in sheet2 column E I get no matches. It might be helpful for someone testing VBA to have results to test it against.
AND, I tested your OBA number in Sheet2 to see if there are any dups and there were none but you have dups of OBA numbers in sheet1. So what would the results look like?

**kashaikh78** · 12-04-2019, 04:23 PM

Sorry. Since it was just a sample of the overall data, there were no duplicates. I now attached a revised spreadsheet with same OBA with different customers.

Hope this can help.

**CARACALLA** · 12-04-2019, 05:38 PM

Sheet1

B4==IF(ISERROR(INDEX(Sheet2!$A$6:$A$3000,SMALL(IF(Sheet2!$E$6:$E$3000=Sheet1!$A4,ROW(Sheet2!$A$6:$A$3000)-ROW(Sheet2!$A$6)+1),COUNTIF(Sheet1!$A$4:Sheet1!A4,Sheet1!A4)))),"",INDEX(Sheet2!$A$6:$A$3000,SMALL(IF(Sheet2!$E$6:$E$3000=Sheet1!$A4,ROW(Sheet2!$A$6:$A$3000)-ROW(Sheet2!$A$6)+1),COUNTIF(Sheet1!$A$4:Sheet1!A4,Sheet1!A4))))

control+shift+enter

copy down

**kashaikh78** · 12-05-2019, 09:30 AM

Great! it's worked.

**Glenn Kennedy** · 12-05-2019, 10:15 AM

kashaikh, it's clear that you are no longer using Excel 2003, as you attached an .xlsx. Please update your profile accordingly. Later Ecel versions may allow for simpler solutions.