Advanced VLOOKUP/MATCH/HLOOKUP Formula

[Darko_Giac]

Hi all,

I am wondering if there is a formula that would be able to take the cells that are populated in the first sheet and copy them into the blank cells that are in the sheet2. However I would like to know if there is a quick way to copy the responses from q1 to q5 in sheet1 and match it to the location of q1 to q5 in sheet2 (a little tricky as the questions in sheet two are spaced differently from those in sheet1).

I have attached an example spreadsheet of what the data look like.

Any suggestions are greatly appreciated.


Thank you!

[Beamernsw]

I think you are asking for a vlookup formula.

Cell B2: =VLOOKUP($A2,Sheet1!$A$2:$F$9,2,FALSE)
Cell F2: =VLOOKUP($A2,Sheet1!$A$2:$F$9,3,FALSE)
Cell J2: =VLOOKUP($A2,Sheet1!$A$2:$F$9,4,FALSE)
Cell N2: =VLOOKUP($A2,Sheet1!$A$2:$F$9,5,FALSE)
Cell R2: =VLOOKUP($A2,Sheet1!$A$2:$F$9,6,FALSE)

and then copy cells B-R down as necessary

[brokenbiscuits]

If you actually have more than 5 questions, the following formula will work on as many columns as you want without needing to adjust each time:

=INDEX(Sheet1!$A:$ZZ,MATCH(Sheet2!$A2,Sheet1!$A:$A,0),MATCH(Sheet2!B$1,Sheet1!$1:$1,0))

The only issue would be you might need to change the 'ZZ' in the formula to expand it if you had more than 50 questions

[Darko_Giac]

Amazing, this worked so well. Thanks brokenbiscuits!