0

I have 2 sheet in excel 2010s: In the 1st Sheet I have all the data and a 2nd sheet where based on the ID's entered(TP) on column A-Sheet2 the formula returns rows from Sheet1.

It looks like this: sheet1 sheet2

Formula used : =IF(COUNTIF(Sheet2!$A:$A,Sheet1!$E1),IF(ISBLANK(Sheet1!E1),"",Sheet1!E1),"")

As it can be seen, in column A from Sheet2 the ID's entered are in a specific order (they are copied from another excel file). Is there a formula that arranges the findings from Sheet2 (column F,G,H) in the same line with Id's from column A-Sheet2?

In the end it should look like: final

Improve this question

1 Answer 1

Reset to default
0

Yes, try this index match match formula:

=IF($A4="","-",INDEX(Sheet1!$A$1:$G$11,MATCH(Sheet2!$A4,Sheet1!$E$1:$E$11,0),MATCH(Sheet2!E$1,Sheet1!$A$1:$G$1,0)))

Paste that into sheet2, cell E5, then copy that cell to E5:H12.

The function focuses on the table in sheet1. Change the coordinates $A$1:$G$11 if your sheet1 table is bigger. The function matches the sheet2 column A value to a row in column E in the table, and then matches the sheet2 row 1 heading to a column in row 1 of the table, "indexing" a single result. The formula is copyable to any row or column on sheet2. Note that column headings must be spelled the same way as the sheet1 column headings.

Improve this answer
2
  • David, you have my gratitude:) , you helped me a lot with this; it saves me a lot of time at work. Have a wonderful week David. Also, I have to thank this "forum" for the help. All the best!
    – othe Bogdan
    Apr 15, 2019 at 18:27
  • Thanks for letting me know, Bogdan! Really glad I helped. Please would you "tick" the answer to mark it as accepted? Then hopefully it'll help others who search for the same problem.
    – David
    Apr 15, 2019 at 20:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .