Multiple Results of Vlookup() - Alternate [VBA Code]

**lloydgodin** · 05-02-2012, 06:33 PM

I have the following Vlookup() in place. The problem is there are multiple values it could return and as Vlookup() is designed to return the first match.

How do I get around this? Is there an alternate function to check for a match in the "range" of matchs instead of the first match.

Please Login or Register  to view this content.

**dilipandey** · 05-02-2012, 11:40 PM

Hi lloydgodin,

You can use Index , Match combination as an array formula to get the multiple matches .. after applying this formula you can use VBA (Macros) to copy - paste special values on the results to avoid formulas.. if you are fine with this, I can show this on Contiguous tab. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**rscsmith** · 05-03-2012, 12:12 AM

Hi lloydgodin

Looking at your code, I think you are trying to test whether the vlookup of body_part(1) matches body_part(2) in the table on the Contiguous sheet.

You could turn this around. Count the number of matches where both body_part(1) and body_part(2) are found on the same row. If it's >0 then you have a match.

In Excel 2007, the COUNTIFS function can achieve this (earlier versions you would be using SUMPRODUCT).

Please Login or Register  to view this content.

Cheers, Rob.

**DiegoBoffa** · 05-03-2012, 12:17 AM

sorry to come here to bother you guys..but i need real urgent help on my thread "Macro to correct cels in column using another column (or w/sheet in the same file) as base"..can someone please give me some help..i'm new here and dont know how to get help

Sorry again and thanks

**rscsmith** · 05-03-2012, 12:58 AM

Hi Diego

You did the correct thing in starting a thread. I think you now have an answer from rylo.

You shouldn't pester on other people's threads as it just confuses matters and pointlessly adds to the number of posts in the forum.

Best regards, Rob.

**lloydgodin** · 05-03-2012, 12:48 PM

Rob,

Remember the Bone song? Y bone is connected to the X bone?? Well that's what contiguous means. The two body parts are connected. The contiguous sheet indicates that the 1st body part is connected to the 2nd body part (thus, making it payable at 50% instead of 75%).

The code is supposed to look up the body part for each procedure (body_part(i) where i is 1 or 2) and see if they're contiguous by matching the contig_part to body_part(2).

So if the body part associated with hcpc(1) is the ankle and the body part associated with hcpc(2) is the skull, they would not be contiguous.

The problem lies with the fact that if the body part associated with hcpc(1) was FACIAL there are 2 other body parts that are considered contiguous. Since the Vlookup() will only return the first match (MANDIBLE), and if the body part associated with hcpc(2) is ORBIT [which per the sheet is contiguous], the code gives a false negative.

**lloydgodin** · 05-03-2012, 06:34 PM

I am thinking my best bet is to do a .Find & FindNext to populate an array. Then if body_part(2) is found in this array then it would be contiguous.

Thoughts/comments?

**rscsmith** · 05-03-2012, 07:24 PM

Hi

Looking at the list of parts, I thought it might be something like that. I just couldn't remember all the words to the song. :-)

Did you try my COUNTIFS code above? It should return 0 if there is no match, i.e. it can't find a row with both body parts in it.

I would tweak it a bit - I would check part 1 in column A and B. So, the new code becomes:

Please Login or Register  to view this content.

Also, I just noted an error in my original - check the first Countifs, which had A1:B38 instead of A1:A38. I've corrected it in this new code.

You could cycle through the whole of column A with a for_next loop:

Please Login or Register  to view this content.

Cheers, Rob.

**lloydgodin** · 05-04-2012, 10:36 AM

Rob,

Forgive me for not understanding your original post. It is actually the code the works the best for what I need. As I read your last post I realized what the code was doing. I did fix the code as directed. And your explanation helped so that I "should" retain the knowledge. LOL

Works wonderfully. THank you.