Excel VBA Course
Excel VBA Course - From Beginner to Expert

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.)

View Course

formula to check multiple criteria in 2 worksheets

0

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

Answer
Discuss

Answers

0

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.

Discuss

Discussion

Thanks John, that worked a treat. 99% of the spreadsheet is spot on, but it has a few random parts that are not working and i cant work out what is different. 

see image below, it randomly just pulls through a Zero instead of the dot.
and on some columns it is pulling through the dot for only a few people, but on the main sheet every person has a dot




but on the raw data page (sheet 1) James has a dot like all of the others. but you can see that Robert and Michelle have dots but they are not transfering to the report.

i have played around with formatting

sue Aug 30, '22 at 9:28 pm
Sue   It took me some time to find it but I now see that you omitted the third parameter of the (second) column MATCH formula (I've now added a note to my answer like this):

"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)."  

Since the second 0 wasn't there, MATCH sometimes got the wrong column (so INDEX gave the wrong result).  

Your formula in B3 was:
   
=IFERROR(INDEX(Sheet1!$A$1:$MM$200, MATCH($A3,Sheet1!$A$1:$A$200,0),MATCH(B$1,Sheet1!$A$1:$MM$1)),"")
  it's now (zero in bold)  
=IFERROR(INDEX(Sheet1!$A$1:$MM$200, MATCH($A3,Sheet1!$A$1:$A$200,0),MATCH(B$1,Sheet1!$A$1:$MM$1,0)),"")
  and copied throughout your sheet. That cotrects thibgs and eliminates the result zeroes too.  

If you also want to remove them from Column A too, make the formula in A3 etc  
=IF(Sheet1!A2<>"",Sheet1!A2"")
so A92 and down appear as blank.

Finally, once you get my new file (second file in Answer) and check it, I suggest you delete the Training... file from your Question (so employee names are not on this public forum). I'll then remove my version from the Answer.
John_Ru (rep: 3992) Aug 31, '22 at 10:54 am
Hope this means you can now mark the Answer as Selected :)

p.s. You're not the only person to attempt to paste an image in the Forum (not allowed) but the first one I recall that spotted it wasn't there and added a file- well done! 
John_Ru (rep: 3992) Aug 31, '22 at 11:01 am
Thanks John, i have it all works, and it looks very clean and profressional now. 
LOL on the Image !! am still trying to naviage and work my way around the site. 
sue Aug 31, '22 at 9:28 pm
Glad that worked for you Sue. I see that you removed the detailed file and I have done likewise.   Please now mark my Answer as Selected - that guides others to good solutions (and adds to your and my "Reputation" on the Forum- my only tangible reward for my efforts to assist you).
John_Ru (rep: 3992) Sep 7, '22 at 5:54 pm
Hi Sue. Hope your sheet is still working well. If so, kindly mark my answer as Selected (to recognise my effort) as I asked above. Thanks in advance. 
John_Ru (rep: 3992) Sep 14, '22 at 5:21 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login