If at first you don't suceed...

[Guest]

Hello everyone,

I have a data sheet that contains a column ('DataSheet'!A4:A103) of data
that I need to compare to a row of data on another sheet ('Sheet1'!F51:AF52).
When a match is found (and there may be multiple matches), I need it to show
the text from the match or matches it finds that are in
('DataSheet'!B4:B104). I would really appreciate any help I can
get......Thanks....Ben

 

[Fred Smith]

You've lost me, so let's take a specific example, say B4.

In B4, do you want to lookup A4 against all entries in F51:AF51?
Suppose it finds a match in Q51. Where does the text come from, Q52?

If so, you would use Hlookup for this, as in
=hlookup(a4,Sheet1!F51:AF52,2,false)

However, this won't solve the problem of needing to identify duplicates. For
that, I would sort your table, then concatenate the text entries. But there may
be other solutions.

Post back with more specifics, so we can identify the proper solution.

 

[Guest]

Hi Fred. Thanks for the reply.
What I'm working on is a time sheet. During our work periods throught the
year we, of course accrue holidays (just like vacation time).
What I'm wanting to do is compare the present work period dates (this is
a cell reference with the =TODAY function), to a list that I manually have
looked up (this is in the ms date format in A4:A103 on the data sheet) and
placed the name of the holiday thats in the cell next to it (that is B4:B103
on the data sheet). I need to place the name of the holiday or holidays that
were accrued (on the time sheet) in a cell to be visually referenced by our
time clerk. Hope this is a help. What I was using is:

=IF(ISNA(MATCH(F51:AF52,'Data Sheet'!A4:A103,0)),"",VLOOKUP(C51,'Data
Sheet'!A4:B103,2))

This only lists the last match that it finds, and not all the matches, which
is what I actually need. I'm about to pull my hair out on this one. Any
help is appreciated. Thanks....Ben

 

[Fred Smith]

You're right -- Vlookup returns only one match.

Is it possible to change your table so that column A has unique entries, and
column B has the results the way you want them displayed?

Alternatively, is column A sorted (or can it be)? If so, you could use Countif
to determine the number of times your entry appears in column A, then use Match
to retrieve each one.

 

[Peo Sjoblom]

You can download an example from here

http://nwexcelsolutions.com/Download/

number six counted from the top

Description here

http://www.nwexcelsolutions.com/advanced_function_page.htm

number 6

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon