Formula assistance

[jrl1208]

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.

 

[AliGW]

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.

 

[jrl1208]

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.

 

[bosco_yip]

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

 

[srinidhi]

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.

 

[jrl1208]

Thank you bosco_yip and Srindhi