looking up 2 values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tim2216 - 29 Oct 2007 21:39 GMT

I have a colum of dates and a colum of websites. What I am trying to do is
lookup
the date and if the date matches. It would then lookup the the webiste and
return the of times it sees the matching date and website to a seperate sheet
under that date:

10/15/07 10/16/07
Monster 4 0
Careerbuilder 6 3

The above is what is what I am trying to.

Reply to this Message

T. Valko - 29 Oct 2007 22:00 GMT

Try something like this:

sites = range of websites
dates = range of dates

=SUMPRODUCT(--(sites=$A2),--(dates=B$1))

Copy across then down

Signature

Biff
Microsoft Excel MVP

>I have a colum of dates and a colum of websites. What I am trying to do is
> lookup
[quoted text clipped - 8 lines]
>
> The above is what is what I am trying to.

Reply to this Message

Bernard Liengme - 29 Oct 2007 22:18 GMT

Try this:
In A1:A10 enter some names (websites)
In B1:H1 enter some dates
In B2:H10 enter some numbers
In K1 enter the name of one of your websites and in J1 enter a data that you
know to be present in B1:B10
In L1 enter this formula
=VLOOKUP(K1,A2:H10,MATCH(J1,B1:H1,0)+1,FALSE)
The MATCH locates the relative position of the date. We add one since our
data base begins in A not B. The Vlookup finds the website name and returns
the number from the correct column
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

>I have a colum of dates and a colum of websites. What I am trying to do is
> lookup
[quoted text clipped - 8 lines]
>
> The above is what is what I am trying to.

Reply to this Message

tim2216 - 30 Oct 2007 11:50 GMT

Decided to do this in colums because of how the data is coming into the page.
So column a is where the dates will be and column b is where the website name
will be listed. Here is the conde I am using, I am getting a #ref error.

=VLOOKUP(S7,A4:A1000,MATCH(T3,B4:B1000,0)+1,FALSE)

s7 is the date that I am looking for and t3 is the name of the website I am
looking for. Any ideas?

> Try this:
> In A1:A10 enter some names (websites)
[quoted text clipped - 20 lines]
> >
> > The above is what is what I am trying to.

Reply to this Message

Bernard Liengme - 30 Oct 2007 13:01 GMT

In your original post you had website names in a column and dates in a row
along the top. This would allow a 2D lookup.
Now you are using A1:A100 for dates and B1:B100 for dates - not two
dimensional. Do you really have websites names in A and dates in B?
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

> Decided to do this in colums because of how the data is coming into the
> page.
[quoted text clipped - 36 lines]
>> >
>> > The above is what is what I am trying to.

Reply to this Message