need help in VLOOK

  • Thread starter Thread starter SMILE
  • Start date Start date
S

SMILE

hello everyone
Hope someone help me to solve my problem.
I have a table in column A & B a range of data. and I have th
following formula in D1
IF($C$1="","",VLOOKUP($C$1,$A$1:$B$10,2,FALSE))
it works ok for one record.

What i need is the following.

In my colum A, there are some items repeating and I need to show th
result in D1,D2,D3....

For eg: if I have the name "AAA" in A1 and in A5, I want th
corresponding data in column B1 and B5 to be displayed in D1 and D2

Hope i explained well
Thanks
Tom
 
Hi Smile!

Try:
IF(C1="","",VLOOKUP(C1,$A$1:$B$10,2,FALSE))

Just remove absolute referencing fromC1.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Norman,

I am sorry, my explanation was not clear for you I believe.

Here is my example
My data is in A&B only
In colum A, I have data by product name(which can be duplicate)
In colum B, I have date of purchase.

In C1, I will be entering a product name and my formula is in D1,D2
D3 ..... (IF($C$1="","",VLOOKUP($C$1,$A$1:$B$10,2,FALSE))

What I need is the date of all the purchase of the product entered i
C1

For eg. I have a product called "BELLS" which purchased three times
and as soon as I entered "BELLS" in C1, I want the corresponding dat
in B to be appreaed in D1, D2 & D3.

hope it is clear for yo
 
Why not just do a Data > Filter > Autofilter on col A?

Then you can just click on the product name from the drop list
in A1, and the filtered rows will be exactly what you're after

And you can also easily copy the filtered rows elsewhere
if necessary via a copy > paste special > check "values"

Looks perfect for the job.
 
Hi Max
I agreed it can be done by autofilter.. but the file has too many link
and i need this part to be restricted to the user.

The user will not have the data- I want the user only to search it
Hope to see some way...need badly
thanks.

Toms
:mad
 
Give this a try:

Assuming your table is in Sheet1, cols A and B,
data from row2 down, with:
col A = Product
col B = Date of Purchase

In a new sheet, say, Sheet2
--------------------------------
Put the labels:
in A1: Product
in B1: Date of Purchase

Cell A2 will be where the product name will be input

Put in C2:
=IF(ISERROR(OFFSET(Sheet1!$A$1,SEARCH("*"&$A$2&"*",Sheet1!A2)+ROW()-2,1)),0,
OFFSET(Sheet1!$A$1,SEARCH("*"&$A$2&"*",Sheet1!A2)+ROW()-2,1))

Copy C2 down as many rows as you have data in Sheet1

(This col C will be hidden eventually)

Put in B2: =LARGE(C:C,ROW()-1)

Copy B2 down as many rows as you have data in Sheet1

[The above will move all the "valid" dates -- for matched rows in col C --
to the top* and shift all the zeros - for unmatched rows in col C -- to the
bottom]
*In descending order

Now to dress up col B ..

Select col B
--------------
Click Format > Cells > Number tab > Date > Select as desired under "Type:" >
OK

With col B still selected:
---------------------------
Click Format > Conditional Formatting

Under Condition 1, make the settings:
Cell value is | equal to | 0 < type a "zero"

Click Format > Font tab > Color > choose "White"** > OK
**or the color matching the fill / background color

Click OK at the main dialog

(This will mask all cells with "zeros" in col B)
 
Max
You are a Genius!!!!!!!!!!!!!!!!!!!!!!!!
It worked so perfect !!!!!!!
I need to look at those Functions and learn now....
Thank you so much....
Toms
 
Toms,

Just some finer tweaks to the formula in C2

Instead of
Put in C2:
=IF(ISERROR(OFFSET(Sheet1!$A$1,SEARCH("*"&$A$2&"*",Sheet1!A2)+ROW()-2,1)),0,
OFFSET(Sheet1!$A$1,SEARCH("*"&$A$2&"*",Sheet1!A2)+ROW()-2,1))
Click to expand...

Put in C2:

=IF(OR(ISBLANK($A$2),ISERROR(OFFSET(Sheet1!$A$1,SEARCH("*"&TRIM($A$2)&"*",Sh
eet1!A2)+ROW()-2,1))),0,OFFSET(Sheet1!$A$1,SEARCH("*"&TRIM($A$2)&"*",Sheet1!
A2)+ROW()-2,1))

Amendments are essentially:

Added an ... ISBLANK($A$2) ... check on the input cell A2
[in case the input cell A2 is "cleared" via Delete key, this gives a cleaner
look in col B]

Wrapped .... TRIM($A$2) ... around the input cell A2
to make the matching more "robust"
[in case any inadvertent leading/trailing spaces are keyed-in with the
product name in A2]
 
Toms, you're welcome !

Glad to know it worked for you.

I've just posted a revised formula for C2
(just some finer tweaks)

Use the revised formula instead.
 
Back
Top