Excel Formula - Hlookup & Vlookup - Xtreme Visual Basic Talk

11-02-2005, 03:01 PM

swoozie

Junior Contributor

Join Date: Dec 2003

Location: PA

Posts: 380

Excel Formula - Hlookup & Vlookup

I am trying to do the following:

Example:

Item Yr1 yr2 yr3
Jim 5 6 7
Jon 8 9 10
Joe 23 8 19

I want to be able to pull data into another spreadsheet based on the item and the year

Item (User input Yr)
Jim (Data)

I was trying to use the sum product function but I can not seem to get it to work.

=SUMPRODUCT((DATA2!$B$2:$B$21292=$B$3)*(DATA2!$D$1:$D$21292=$C$2)*(Thi s is the part I can not get figured out)

Any help?

__________________
Swoozie

Somedays you just shouldn't get out of bed in the morning.

11-02-2005, 04:36 PM

Kluz

Sapience.Aquire

Super Moderator
* Expert *

Join Date: Oct 2003

Location: Lake Bluff, Ill., U.S.

Posts: 3,306

I don't understand your question. Could you give us an example of the input and expected output? Your title: Hlookup & Vlookup, how does that fit in?
Also, check out the MATCH worksheet function.

__________________
No the other right mouse click

11-03-2005, 02:17 AM

planetwise planetwise is offline

Regular

Join Date: Jun 2004

Location: Groningen, Netherlands

Posts: 75

Is this of any help?

Try this:

=OFFSET(Reference-cell;MATCH(Vertical-searchvalue;search-matrix;0);MATCH(Horizontal-searchvalue;search-matrix;0))

In your case:

Code:

=OFFSET(A1;MATCH("Jim";A2:A4);MATCH(UserinputYR;B2:D2)

where UserinpuYR is user input Yr

John

__________________
Don't declare my string in public, it isn't integer.

Last edited by planetwise; 11-03-2005 at 02:30 AM.

11-03-2005, 07:04 AM