200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
i run a report daily and the colums are not in the order i want them, so i have an excel that is formatted in the layout i want.
i then need to pull the results that match each row and colum in 1 sheet and display in my formatted sheet.
sample attached
if sheet1 has a symbol (column C has heading d and row 3 has number 3 then i want the * to show in sheet2 in cell B4 which is heading a and number 3
Hi Sue and welcome to the Forum.
I think the best way to solve your problem is using INDEX and MATCH functions, specifically the Array form. This is a bit tricky to understand at first but please read this answer, check the file then watch one or more of Don's lessons on that in the Tutorials page, e.g. Best Lookup Formula in Excel - Index and Match.
In the attached revised file, I've put this formula in cell A2 of Sheet2. Essentially it cross-references the value in the row and the value in the column within the array on Sheet1:
=INDEX(Sheet1!$A$1:$E$6, MATCH($A2,Sheet1!$A$1:$A$6,0),MATCH(B$1,Sheet1!$A$1:$E$1,0))The $ signs are important since they fix the row or column or both. The zeros (0, in bold above) force the MATCH formula to find an exact match (or return an error if not). If you omit that zero, MATCH finds the largest value that is less than or equal to lookup_value (which might give unexpected results if you're expecting or need an exact match).
That formula is copied to the cells with borders (only) and you'll see it puts the * and % in the right cells. Also I typed "Sue" in E6 of Sheet1 (column heading c, row 4) and it appears in D5 of Sheet2 (which has those headings). You can try other entries of course.
Note that if the formula finds a blank at the cross-referenced cell, it returns a 0. I've suppressed that in the cells with bordersof Sheet2 (only) by setting a Custom format for those cells as:
0;-0;;@which displays those zeroes as blanks. Alternatively you could set the Advanced option in Excel to not display zeroes at a workbook level (by unchecking the "Show a zero in cells that have zero value" box).
You might need to wrap an IFERROR around my formula if there are cases where row or column headings in Sheet2 are not in Sheet1.
Hope this makes sense and helps. Please don't forget to mark this Answer as Selected if so.
REVISION: For a short time, I'm attaching a second file which corrects your implementation of the formula above). Tis will be deleted once you have it.
=IFERROR(INDEX(Sheet1!$A$1:$MM$200, MATCH($A3,Sheet1!$A$1:$A$200,0),MATCH(B$1,Sheet1!$A$1:$MM$1)),"")=IFERROR(INDEX(Sheet1!$A$1:$MM$200, MATCH($A3,Sheet1!$A$1:$A$200,0),MATCH(B$1,Sheet1!$A$1:$MM$1,0)),"")=IF(Sheet1!A2<>"",Sheet1!A2"")