[smcgartland]

I am trying to lookup a parcel number ( XX-XX.XX-XXX-X) out of a column with miscellaneous text and return the value from an adjacent cell. I can make it work using =IF(ISNUMBER(SEARCH(E4,B3)),A4,"FALSE") for one specific line, but it gives me an error if I change B2 to B:B. the VLOOKUP doesn't want to work I'm assuming because of the text. I have approximately 1,500 parcel numbers that I need to pull out of the comments section to match with another list of parcel numbers.

Does anyone have any suggestions? Any help would be appreciated.

Thanks,

 

[RSpecianjr]

Re: VLOOKUP and/or ISNUMBER(SEARCH(

Hey smcgartland,

Can you give us some examples of the cells you are searching through and how the values you are looking up are presented?

If you could post a dummy workbook with at least 10 examples, that will give us something to work with. It's just easier for us to see it than try and think theoretical.

Regards,

Robert D. Specian Jr.

 

[smcgartland]

Re: VLOOKUP and/or ISNUMBER(SEARCH(

Thanks for such a prompt reply. Here is a sample spreadsheet. I'm trying to take the Parcel numbers in Column C and extract the File No from Column A based on matching the Parcel number within Column B. As you can see in Row 4 the format in Column B is not always the same, also sometimes there are multiple parcel numbers listed in the Comments (Column B) as in Row 15 so text to columns, or MID(x,x) is not a realistic option.

Does this help?

 

[RSpecianjr]

Re: VLOOKUP and/or ISNUMBER(SEARCH(

Hey smcgartland,

I guess I am unsure what you are trying to do. Try something like this though:

=SUMPRODUCT(NOT(ISERROR(FIND(C2,B$2:B$16,1)))*A$2:A$16)

Just put this formula in D2 and copy it all the way down. Let me know if this is what you are looking for.

Regards,

Robert D. Specian Jr.

 

[smcgartland]

Re: VLOOKUP and/or ISNUMBER(SEARCH(

Robert,

Thank you. This works exactly as I need it to. I'm having difficulty in understanding the =SUMPRODUCT function. If you have a free moment, would you please briefly explain each part of the formula and why you used it?

I really appreciate your help with this, thank you again.

Sherry

 

[RSpecianjr]

Re: VLOOKUP and/or ISNUMBER(SEARCH(

Hey smcgartland,

Alright, simply put sumproduct applies formulas within respective ranges. Very similiar to an Array formula, but less calculation expensive.

Let's break down the formula. You really have two things that you are multiplying times each other:

NOT(ISERROR(FIND(C2,B$2:B$16,1)))

and

A$2:A$16

First step is FIND(C2,B$2:B$16,1). It creates a list of numbers and errors depending on if C2 is found in B2,B3,B4...B16. It would look something like: #value, #value, #value, 12, #value.... etc for each B2 to B16.

Next we add ISERROR, which will make those values turn into TRUE or FALSE. So: True, True, True, False, True.... etc for each B2 to B16.

Next we reverse TRUE and FALSE with the NOT() formula. So, FALSE, FALSE, FALSE, TRUE, FALSE... etc for each B2 to B16.

When you are multiplying a true and false statements against numbers, TRUE = 1 and FALSE = 0. So you have: 0,0,0,1,0.... etc for each B2 to B16.

Last you multiple those against the values within A2 to A16 respectively, then add them all up. It will work for you unless there is an istance where one lookup number is found in multiple cells.

Hope tha makes sense!

Regards,

Robert D. Specian Jr.

 

[Glaswegian]

Some great explanations and discussions of SUMPRODUCT here

SUMPRODUCT Step By Step - Part 1 - VBA Express Forum
SUMPRODUCT Step By Step - Part 2 - VBA Express Forum
SUMPRODUCT Step By Step - Part 3 - VBA Express Forum

 

[RSpecianjr]

If you are going to use a lot of complex SUMPRODUCT Formulas, coercion is a good thing to understand .

Great posts Iain!

Robert

 

[smcgartland]

Thank you both, Robert and Iain. I guess I still have a lot to learn yet.

Sherry