Vlookup with Multiple lookup values

[iceman_574_98]

i was trying to use
{=IFERROR(INDEX('Total AR'!$B$1:$B$9999,SMALL(IF($A$1='Total AR'!$A$1:$A$9999,ROW('Total AR'!$B$1:$B$999)-2,""),COLUMN()-9999)),"")}
In which Total AR Column B has a unique number in it, Total AR Column A has either AR or EXPENSE or OTHER in it.
on my current sheet A1 i will enter either AR or EXPENSE or OTHER and i want in A2 through A60 for it to get each one of the unique numbers from my Total worksheet that match the entry i put in A1.
So that i put AR in A1 , i would put formula in A2 that would bring over the first unique value from total tab that has AR in column A on the worksheet, A3 would have the next unique number and so on.

 

[StephenCrump]

Welcome to the Forum!

I think this is what you're trying to do?

	A
1	EXPENSE
2	77
3	88
4	99
5
6
Sheet1

Cell Formulas
Range	Formula
A2:A5	A2	=IFERROR(INDEX('Total AR'!$B$6:$B$9999,SMALL(IF(A$1='Total AR'!$A$6:$A$9999,ROW('Total AR'!$B$6:$B$9999)-ROW('Total AR'!A$6)+1),ROWS(A$2:A2))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

	A	B
1
2
3
4
5
6
7	EXPENSE	77
8
9	EXPENSE	88
10
11	OTHER	22
12
13	EXPENSE	99
14
Total AR

I suggest you update your Account details so we know what Excel version(s) & platform(s) you are using - the best solution often varies by version.

 

[Peter_SSs]

I suggest you update your Account details so we know what Excel version(s) & platform(s) you are using - the best solution often varies by version.

Further to Stephen's suggestion ..

IF you have a recent version with the FILTER function you would only need this formula in the top cell.

iceman_574_98.xlsm
	A
1	EXPENSE
2	77
3	88
4	99
5
Sheet1

Cell Formulas
Range	Formula
A2:A4	A2	=FILTER('Total AR'!B1:B9999,'Total AR'!A1:A9999=A1,"")
Dynamic array formulas.