HELP with vlookups

[pbyrne462]

Hi there,

I have a work book which on worksheet 2 I download a set of data from a database. In worksheet 1 is a table which I use to present this information in a clean manner.

I need to know how to do the following:

Worksheet 1

A1 = Product
B1 = Country
C1 = Stage

D1 = desired cell for the formula

Worksheet 2

A1 = Product
B1 = Country
C1 = Stage
D1 = date

I need in D1 a formula that will look at worksheet 2 column A B and C compare the data to worksheet 1 in cells A1, B1 and C1 and if they match return the date in cell D1 of worksheet 2 to D1 in worksheet 1.

am i going crazy i cant make this work

 

[Jonmo1]

Welcome to the board...

I would suggest adding a helper column in Worksheet2
Assuming you have headers in row 1, formula starts in row 2.

IN E2 on Worksheet2 put
=A2&B2&C2
And fill it down all the way to end of your data.

IN Worksheet1, D2 (again assuming headers in row 1) put
=INDEX('Worksheet 2'!D$2:D$1000,MATCH(A2&B2&C2,'Worksheet 2'!E$2:E$1000,0))

Hope that helps.

 

[Jonmo1]

To error proof it a bit...

IN E2 on Worksheet2 put
=A2&"|"&B2&"|"&C2
And fill it down all the way to end of your data.

IN Worksheet1, D2 (again assuming headers in row 1) put
=INDEX('Worksheet 2'!D$2:D$1000,MATCH(A2&"|"&B2&"|"&C2,'Worksheet 2'!E$2:E$1000,0))

 

[T. Valko]

Hi there,

I have a work book which on worksheet 2 I download a set of data from a database. In worksheet 1 is a table which I use to present this information in a clean manner.

I need to know how to do the following:

Worksheet 1

A1 = Product
B1 = Country
C1 = Stage

D1 = desired cell for the formula

Worksheet 2

A1 = Product
B1 = Country
C1 = Stage
D1 = date

I need in D1 a formula that will look at worksheet 2 column A B and C compare the data to worksheet 1 in cells A1, B1 and C1 and if they match return the date in cell D1 of worksheet 2 to D1 in worksheet 1.

am i going crazy i cant make this work

Try this array formula**:

=INDEX(Sheet2!D1:D100,MATCH(1,IF(Sheet2!A1:A100=A1,IF(Sheet2!B1:B100=B1,IF(Sheet2!C1:C100=C1,1))),0))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Adjust the ranges to suit.

Format as Date (if needed).

 

[pbyrne462]

Thank you, that works a treat. I have another problem that is bothering me.

I have a column of dates grouped 8 at a time. I need to copy the 8 cells and transpose them into another sheet, in order to show them horizontally. That is easy to do manually but I want to be able to do this repeatidly 8 cells at a time using a marco if possible. Any ideas??

 

[T. Valko]

Thank you, that works a treat. I have another problem that is bothering me.

I have a column of dates grouped 8 at a time. I need to copy the 8 cells and transpose them into another sheet, in order to show them horizontally. That is easy to do manually but I want to be able to do this repeatidly 8 cells at a time using a marco if possible. Any ideas??

Try starting a new thread for this.