Nesting other functions with vlookup

[Noobllianz]

Hi peeps!

I'm trying to nest a double vlookup with a search function to search partial words.

Any help!



A B C D E F
1 Customer Name Customer No# Order # Order Price Order Date Description
2 John Doe Cust001 1000item $1000 2011/01/01 Laptop
3 Jane Doe Cust002 2000item $2000 2012/01/01 Monitor
4 John Smith Cust003 3000item $3000 2013/01/01 Motherboard
5 Jane Smith Cust004 4000item $4000 2014/01/01 Keyboard
6 Doe Smith Cust005 5000item $5000 2015/01/01 Mouse
7 John Doe Cust001 6000item $6000 2016/01/01 SSD
I am currently using

=IFERROR(VLOOKUP(SMALL(IF(IF(ISERROR(SEARCH($Z$1,$A2:$A7&" "&$B$2:$B$7)),FALSE,TRUE),ROW($C$2:$C$7)),$Y2),CHOOSE({1,2},ROW($C$2:$C$7),$A2:$A7),2,0),"")

How can I double vlookup within this similar formula.

Thank you

 

[Special-K99]

In words what are you trying to achieve?

 

[Noobllianz]

In words what are you trying to achieve?

I was trying to look up the name of the customer by the first name or last name or customer No# and displaying all possible results.

	A	B	C	D	E	F
1	Customer Name	Customer No#	Order #	Order Price	Order Date	Description
2	John Doe	Cust001	1000item	$1000	2011/01/01	Laptop
3	Jane Doe	Cust002	2000item	$2000	2012/01/01	Monitor
4	John Smith	Cust003	3000item	$3000	2013/01/01	Motherboard
5	Jane Smith	Cust004	4000item	$4000	2014/01/01	Keyboard
6	Doe Smith	Cust005	5000item	$5000	2015/01/01	Mouse
7	John Doe	Cust001	6000item	$6000	2016/01/01	SSD

<tbody>
</tbody>

So if i were to search for "John", all results will be shown.

My formula runs super laggy and I heard that a Double vlookup can speed it up when there is over 100k lines of data.


Would you say this is the best way to get the multiple results?

Thanks for responding!

 

[Special-K99]

If you can use AGGREGATE

with G1 your search name or G2 your Cust ID

in H1
=IFERROR(INDEX(A$2:F$7,AGGREGATE(15,6,ROW(A$2:A$7)/((G$1<>"")*(ISNUMBER(SEARCH(G$1,A$2:A$7)))+(G$2<>"")*(G$2=B$2:B$7)),ROWS(A$2:A2))-(2-1),1),"")
and copy down

If you copy across the row to column M and increase the number in red by 1 each time you should get the whole row
or replace the 1 with COLUMN()-7 and copy across to column M

 

[Noobllianz]

If you can use AGGREGATE

with G1 your search name or G2 your Cust ID

in H1
=IFERROR(INDEX(A$2:F$7,AGGREGATE(15,6,ROW(A$2:A$7)/((G$1<>"")*(ISNUMBER(SEARCH(G$1,A$2:A$7)))+(G$2<>"")*(G$2=B$2:B$7)),ROWS(A$2:A2))-(2-1),1),"")
and copy down

If you copy across the row to column M and increase the number in red by 1 each time you should get the whole row
or replace the 1 with COLUMN()-7 and copy across to column M

Thank you for your reply!

I will try it first thing tomorrow at work!

Thank you for your suggestion.

Appreciate it.