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

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
Thank you bosco_yip and Srindhi