Help with formula

[Jack of Diamond]

I have a list of values on sheet 2 as follows; A2=X, A3=Y, A4=Z. B2=1, B3=4, B4=7. C2=2, C3=5, C4=8. D2=3, D3=6, D4=9. Can a formula be written so that if the letter X,Y or Z is entered in any random cell of column A in sheet 1that columns B,C and D will automaticly display the corrisponding information? Example; I would type Y in any cell in column A in sheet 1 and column B,C and D would display 4,5 and 6.

[davesexcel]

Checkout Vlookup
http://www.freewebs.com/davesexcel/V...20Example2.xls

[Gerry1206]

This can be done with vlookup formulas. Column B,C,D requires a formula in sheet 1.

They need to look like this: Column A is the into, start at A2 and in:

B2: =VLOOKUP($A2,Sheet2!$A$2:$D$4,2,FALSE)
C2: =VLOOKUP($A2,Sheet2!$A$2:$D$4,3,FALSE)
D2: =VLOOKUP($A2,Sheet2!$A$2:$D$4,4,FALSE)

Paste this down column B to D for as many as you need in A.

Good luck.

Gerhard

[daddylonglegs]

Put this formula in sheet1 B1 and copy across to D1 and down as far as required

=IF(COUNTIF(Sheet2!$A$2:$A$4,$A1),INDEX(Sheet2!B$2:B$4,MATCH($A1,Sheet2!$A$2:$A$4,0)),"")

[shg]

Jack, please read the Forum Rules about thread titles before starting your next thread.

[Jack of Diamond]

Thanks. I used the advice of daddylonglegs and it worked. I will do a better job naming my next post.

[shg]

Thank you, Jack, I appreciate your attitude.