MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Vlookup Help


Posted by Chris Wheeler on May 17, 2001 5:45 AM

Does Anybody Know If You Can Use Vlookup To Return Multiple Values. If you Are Searching For A Value That Appears In More Than One Row How Can You Get Vlookup To Keep Going After It Finds The First Occurance?


Posted by Mark W. on May 17, 2001 6:22 AM

VLOOKUP() can return values from multiple columns --
not rows.

Posted by Benedick Dogberry on May 17, 2001 6:36 AM

Chris
Well....there are many ways to do this (with and without VBA). A simple non-VBA way is as follows but I'm sure someone will provide something more sophisticated.

If your look-up values are in column A and the values to be returned are in column B, enter the following formula in C1 and fill down as far as required :-
=IF(ISNA(VLOOKUP(your_lookup_value,A1:B1,2)),"",VLOOKUP(your_lookup_value,A1:B1,2))

You can then manipulate the values in column C as required.

BD

Posted by Kevin James on May 17, 2001 8:38 AM

Chris,

It appears both the other respondants didn't understand your request. You want to find multiple hits with VLOOKUP. Answer: VLOOKUP was not designed for that purpose. It was designed to find a unique record and return details about that record.

To better understand your needs, would you please post what the scenario is you are working with.

Kevin

Posted by Chris Wheeler on May 17, 2001 8:47 AM

Re: Example Problem

I Would Like To Know How To Return Multiple Values.

Here Is A Short Example.

Column A Is Part Numbers
Column B Is Product Description

I Have Been Using Vlookup To Search Column A For A Part Number Match And Then Return The Description.

My Problem Is That I Have Many Part Numbers That Are The Same.

Vlookup Only Returns The Description From The First Part Number It Finds.

Is There A Way To Search Through An Entire Worksheet And Return All Of The Matching Results?

Posted by K on May 17, 2001 8:51 AM

Re: Example Problem

Posted by Kevin James on May 17, 2001 8:52 AM

Re: Example Problem

Let me think about it. I've done something like this before, but its been a while.

Kevin

Posted by Mark W. on May 17, 2001 10:15 AM

> It appears both the other respondants didn't understand your request.

I understood quite well, and responded that
VLOOKUP() will allow you to return multiple
column values -- not multiple row values.

Posted by Benedick Dogberry on May 17, 2001 3:24 PM

Re: Example Problem


Have a look at 17330.html
Alrenatively, put the following formula in C1 and fill down :-
=IF(A1="part number",B1,"")