Formula Help

[Greg Feddersen]

Hi,

I need some help with a spreadsheet that needs to do the following every
time I copy and paste a report.

I paste data into cell A1 and I need it to look through cells AA1:AA100 to
find a match, then take the 4 cell's data to the right and paste it to B1,
C1, D1 & E1.

Thanks in advance.

Greg

 

[J.E. McGimpsey]

One way:

B1: =VLOOKUP(A1,AA1:AE100,2,FALSE)
C1: =VLOOKUP(A1,AA1:AE100,3,FALSE)
D1: =VLOOKUP(A1,AA1:AE100,4,FALSE)
E1: =VLOOKUP(A1,AA1:AE100,5,FALSE)

 

[ryanb.]

Here you go, put this in B1, C1, D1, and E1

=IF(ISNA(INDEX(Sheet1!$AA$1:$AE$100,MATCH(A1,Sheet1!$AA$1:$AA$100,0),5)),"NO
MATCH",INDEX(Sheet1!$AA1:$AE$100,MATCH(A1,Sheet1!$AA$1:$AA$100,0),5))

This is assuming by 4 columns over you mean a total of 5 columns (4 cells to
the right of AA is AE. if the data you want is in AD, change the 5 in the
match function to 4. I also used absolute references on AA1 to AA100. if
you wan them truly dynamic, remove the $'s and remove the rows numbers.

HTH

ryanb.