Returning the 1st. non blank value for a look up, even if lookup is only a partial match

[PrometheusOdinson]

Hi all,

I'm pretty stumped and hoping someone can help. Attached a sample workbook.

I've got two tables, Table 1 and Table 2. Both list companies and contact persons. Table 2 is missing contact persons, and I need to pull that information based on the names, which will be looked up in Table 1.

The problem is, the names in Table 1 may have slightly different wording or spellings, so I need a match even based on a partial name match. The other main problem is, some companies listed in table 1 multiple times may have blank cells for their associated contact person- I need to return the first non blank contact person.

I tried using a combination of Vlookup with WILD card functionality first, and then I also tried using a formula I found online to return the first non blank match


=LOOKUP(2,1/(INDEX(($A$3:$A$12=LOOKUP VALUE)*($B$3:$B$12<>""),0)),$B$3:$B$12)

If I could somehow combine these formulas that would solve the problem, or so I think.

I've also been asked why I don't just clean up table 1 and use that instead of table 2, but this is a simplified example and there are other columns in Table 2 that I need, as well as the fact table 1 has a list of ALL possible companies, not just the ones I'm working with in table 2.

Thanks.

[bebo021999]

Try in H3:

Please Login or Register  to view this content.

[Fluff13]

Another option

Please Login or Register  to view this content.

[PrometheusOdinson]

I was pretty excited when I tried this on the sample set, since it worked, but using this on my actual data set still unfortunately returned a lot of #N/A values

[PrometheusOdinson]

This worked on the sample data, and did work for some of my actual data, but it returned a lot of #CALC errors for some reason. Not sure how to proceed.

[Fluff13]

If you are getting the #calc error then there is no matching data. You can show a blank instead of the error like

Please Login or Register  to view this content.