I have Excel file with four sheets.

How to make duplication using Excel built-in function using specific parameters?


Sheet 1 - Total:

         A            B          C
1    Iveco         100          4.5
2    Jaguar        200          2.3
3    Iveco         150          1.0
4    Skoda         300          1.0

Sheet 2 - Iveco:

      A            B
1    100          4.5
2    150          1.0

Sheet 3 - Jaguar:

      A            B
1    200          2.3

Sheet 4 - Skoda:

      A            B
1    300         1.0

So if I wrote at cell A5 in Sheet 1 - Total value "Iveco", automatically wrote values from cells B5, C5 to the Sheet 2 - Iveco, cells A3, B3.



My VLOOKUP in Sheet 2 - Iveco:


I have a formula that should work for you that I've used in similar situations.

Use this formula in your non-Total sheets. It'll grab the sheet name, and look for that in your Total sheet, then return the column B (or whatever you put in the Index()) info. without repeating:


The range Total!B1:B4 should be the range of data you want to return. Total!A1:A4is the list of sheet names.

Enter this with CTRL+SHIFT+ENTER

  • OK I test tomorrow, because is too late in my country. :-) Jan 29 '16 at 21:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.