• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula assistance

jrl1208

Member
Hi
I’m currently using a vlookup formula in my summary tab for each of my heading in Cell E1 to H1.
For the vlookup to work I need to sort the data by type first. Is there a better formula to use without the need to sort the data by type. Any assistance will be greatly appreciated. Thank you.
 

Attachments

  • Test data.xlsx
    14.6 KB · Views: 8
Try this:

=INDEX(Data!$I$3:$I$51,MATCH(1,(Data!H3:H51=$A2)*(Data!G3:G51=E$1),0))

Enter using CTRL+SHIFT+ENTER, not just ENTER. If you have MS365, then you could use the FILTER function.
 
Hi Ali

Thank you so much for the formula. However, when I applied the formula, it didn't work on all the cells.
Please refer to the attached excel spreadsheet Cell E17

Thank you.
 

Attachments

  • Test dataR1.xlsx
    17.9 KB · Views: 3
Hi,

Change your formula In E13 >>

From this :

=IFERROR(INDEX(Data!$I$3:$I$51,MATCH(1,(Data!H3:H51=$A13)*(Data!G3:G51=E$12),0)),0)

Into this (with highlighted in red in adding "$"):

=IFERROR(INDEX(Data!$I$3:$I$51,MATCH(1,(Data!$H$3:$H$51=$A13)*(Data!$G$3:$G$51=E$12),0)),0)

Also

Enter using CTRL+SHIFT+ENTER, not just ENTER.

Then

Copied formula across right and all copied down
 
Hi,
Have put table on the unsorted data and pivot from the table, you can use the pivot or you can VLOOKUP from the pivot to your desired sheet.
Just refresh the pivot to get the newly added data.
 

Attachments

  • Test data.xlsx
    23.1 KB · Views: 0
Back
Top