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.


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


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.