Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

User Input Lookup Table Array Formula

0

This is a follow up to my last post about how to create a lookup table with user input with a results for each match. 

I have come up with the user input, lookup table, and results. My issue is that it will only give me the results that it matches in B2 if they are EXACT matches. 

What I need is something that will autopopulate in column G:G depending on the matches. (currently I have G2:G5) which should be sufficient but I don't want to leave room for error. I know that Partial matches are not a perfect science, but this will narrow the user's choices down and make their lives much easier. 

excelformulas-functions
MS Office Version: Sroncey21
posted by: Sroncey21 (rep: 66) on Mon Dec 10, 2018 at 8:49 pm
Answer
Discuss

Answers

0
Selected Answer

Thanks to your persistence, I think we now nailed it. Please try this formula.

=IF(OR($G1=REPT("-",25),$G1=""),"",IFERROR(INDEX(Destins, SMALL(IF(IF(ISERROR(SEARCH($B$2,PolComms)),0,1), ROW(PolComms)-MIN(ROW(PolComms))+1, ""),ROWS($G$2:$G2))),REPT("-",25)))

It's an array formula. Confirm with Shift+Ctl+Enter.

I named the following ranges (addresses from your trial worksheet).
PolComms = $D$2:$D$164
Distins = $E$2:$E$164

$G$2:$G2 is the cell in which the formula should be entered.
$G1 is the cell above the cell in which the formula is entered.
These references must be changed in the formula if you enter it elsewhere.
G1 (the cell above the formula) may not be blank.

After entering the formula, you can copy it down for as long as you wish. The formula will draw a line under the last found match. If a result doesn't have a line at the bottom there are more matches than there are formulas.

posted by: Variatus (rep: 4889) on Mon Dec 10, 2018 at 11:22 pm
Discuss

Discussion

I like what you did with the lines! I've entered the formula down and copied it down. I've tried several examples in B2 User Input to test and I can only return the "----" lines. I hit CTRL+SHIFT+Enter and still the same lines result. Something i'm doing wrong? I've attached my sheet with the formula inside that you had provided. 
Sroncey21 (rep: 66) Dec 10, '18 at 11:34 pm
You didn't define the named ranges. I suggest you do. But if you really don't want, replace the range names with the range addresses in the formula. The easiest way to name a range is to select it and write the name in the Name Bar (to the left of the Formula Bar). Then use name manager (Formulas tab) to modify.
Variatus (rep: 4889) Dec 10, '18 at 11:47 pm
It worked! Thank you so much!!!!
Sroncey21 (rep: 66) Dec 10, '18 at 11:53 pm
Really glad, I am! Good teamwork it was. :-)
Variatus (rep: 4889) Dec 11, '18 at 12:04 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login