V/LOOKUP dilema!

[Guest]

Hi,

This is the first time I'm using these formulas and can't seem to get it
right.

I have two tables. Table A and Table B.
The one thing they have in common is a date and a file name (in different
columns.)

I concatenated the date and file name columns, making it all uppercase. I
need to take a date & file combo 1 in col C1 (e.g. 12/08/2004 HAPPY.DLL) from
Table A and find it in col H:H Table B (e.g. 12/08/2004 HAPPY.DLL) and return
the corresponding value from col J:J (e.g. 1066) Table B. So if it’s found in
H27 it returns the value in J27.

However. When I use Lookup, it sometimes returns data because it does the
whole "search for the closest value" thing. If I use VLookup, I get a whole
bunch of #REF values, even though I know that the dates exist in both tables.
Another problem I encountered was with the dates. When I concatenated the
date and file name, the date changed to the “Excel 10245 format). I used the
DAY(), MONTH(), & YEAR() functions to sort that out.

Some of the formulas I've been using are:

=LOOKUP(C1,H:H,J:J)

=VLOOKUP(C122,H1:H103,J1:J103,FALSE) - this returns either N/A or #REF

Basically, I need it to return a corresponding value, if, and only if the
same date is found.

Any help would really be appreciated.

Thanks.

 

[BenjieLop]

Check if this will work for you ...

=VLOOKUP(C122,H1:H103,2,FALSE)

 

[Frank Kabel]

Hi
try:
=VLOOKUP(C122,H1:J103,3,FALSE)

 

[Guest]

Hi,

Thanks for answering. But it didn't work. I just get #N/A all the way.

 

[Guest]

Hi,

Thanks for answering. But I just get #N/A.

I've just checked to make sure that there are matches in the two different
tables. Both for date and for file name. And there are.

thanks again.

 

[Guest]

When you did the concatenation of the date/file name, did you include a space?

e.g.:

=CONCATENATE(D4," ",E4) or
=CONCATENATE("12/08/2004"," ","HAPPY.DLL")

I am wondering if the two "identical" cells truly are identical?

 

[Guest]

=DAY(K11)&"/"&(MONTH(K11))&"/"&(YEAR(K11))&" "&UPPER(M11)

I used the DAY/MONTH/YEAR functions because when i tried to concatenate the
date cell to the file name cell I get the 5 digit date code from Excel.

Eg. If i type in formula =A122&" "&B122 , I get "38254 de713.txt"

 

[Frank Kabel]

Hi
probably there's a difference in both values though they 'LOOK' the
same. Check for different formating, etc. also try to check manually
with a formula such as
=C122=H2
where H2 is the probably match. This should return tRUE

 

[Dave Peterson]

I haven't read the whole thread, but:

=text(k11,"mm/dd/yyyy") & " Happy.dll"

might work ok for you.

 

[Dave Peterson]

You had day first:

=text(k11,"dd/mm/yyyy") & " Happy.dll"

oops.