Search vlookup more than 1 column

[Bond00]

So right now this function works and it searches another Doc filled with customer names in A2:A1500 range based off the partial value in C1

=IF(ISNUMBER(SEARCH("",VLOOKUP($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A$2:$L$1500,COLUMNS($A$1:A1),0))),VLOOKUP($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A$2:$L$1500,COLUMNS($A$1:A1),0),C1)

this formula is in C4 through C7 (i edited out the other cells) and reads the name from C1 as text to search

right now it searches another doc in col A but i'd like to also search col B2:B1500 and col C2:C1500 in that zCompany List.xlsx as well so it can pull data based on more options from the C1 cell

C1 pulls from somewhere else as well, so sometimes it gets a default value which is why i have it just default to C1 at the end if it doesn't find the searched name in the zCompany List doc, If i remember right i think the =IF(ISNUMBER is for that reason otherwise the C4-C7 cells become #N/A

So remember main goal, to change this formula so it can search A B or C col for data to pull in from the other doc (if it comes across a blank cell in those A-C columns just ignore)

thanks for any help!

 

[Bond00]

yeah that seems to work good now. this is the layout i used. I need to leave in a sec but ill test it more thoroughly later and make 100% sure and ill post back and let you know.
Also i didnt press ctrl + shift enter to put that in, i didn't need to right?

Can i set that to Tool'!$A2:$A1500, and l'!$B2:$B1500,0) just so it doesn't reference the whole columns?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Company Name	Short Srch	Contact Name	Address	City, State Zip	Country	N/A	Phone	Fax	Email	Tax Exempt	Labor Rate	Extra
2	Test company 1	T1	Name1 lname1	1001 place rd	city1, state1 zip1	US1		numb1	fax1	email1	No	0
3	Test company 2	T2	Name2 lname2	1002 place rd	city2, state2 zip2	US2		numb2	fax2	email2	No	0
4	Test company 3	T3	Name3 lname3	1003 place rd	city3, state3 zip3	US3		numb3	fax3	email3	No	0
5	Test company 4	T4	Name4 lname4	1004 place rd	city4, state4 zip4	US4		numb4	fax4	email4	No	0
6	Test company 5	T5	Name5 lname5	1005 place rd	city5, state5 zip5	US5		numb5	fax5	email5	No	0
7	Test company 6	T6	Name6 lname6	1006 place rd	city6, state6 zip6	US6		numb6	fax6	email6	No	0
8	Test company 7	T7	Name7 lname7	1007 place rd	city7, state7 zip7	US7		numb7	fax7	email7	No	0
9	Test company 8	T8	Name8 lname8	1008 place rd	city8, state8 zip8	US8		numb8	fax8	email8	No	0
10	Test company 9	T9	Name9 lname9	1009 place rd	city9, state9 zip9	US9		numb9	fax9	email9	No	0
11	Test company 10	T10	Name10 lname10	1010 place rd	city10, state10 zip10	US10		numb10	fax10	email10	No	0
12	Test company 11	T11	Name11 lname11	1011 place rd	city11, state11 zip11	US11		numb11	fax11	email11	No	0
13	Test company 12	T12	Name12 lname12	1012 place rd	city12, state12 zip12	US12		numb12	fax12	email12	No	0
14	Test company 13	T13	Name13 lname13	1013 place rd	city13, state13 zip13	US13		numb13	fax13	email13	No	0
15	Test company 14	T14	Name14 lname14	1014 place rd	city14, state14 zip14	US14		numb14	fax14	email14	No	0
Sheet1

 

[DanteAmor]

Also i didnt press ctrl + shift enter to put that in, i didn't need to right?

In these formulas it is not necessary

Can i set that to Tool'!$A2:$A1500, and l'!$B2:$B1500,0) just so it doesn't reference the whole columns?

In these formulas it is not necessary, because they are standard formulas. But if you want to narrow it down, then start in row 1.

=IFERROR(INDEX('[zCompany List.xlsx]Contacts for Tool'!$A1:$A500,
IFERROR(MATCH($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A1:$A500,0),
IFERROR(MATCH($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$B1:$B500,0),
IFERROR(MATCH($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$C1:$C500,0),"")))),"Not found")

 

[Bond00]

In these formulas it is not necessary


In these formulas it is not necessary, because they are standard formulas. But if you want to narrow it down, then start in row 1.

=IFERROR(INDEX('[zCompany List.xlsx]Contacts for Tool'!$A1:$A500,
IFERROR(MATCH($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A1:$A500,0),
IFERROR(MATCH($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$B1:$B500,0),
IFERROR(MATCH($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$C1:$C500,0),"")))),"Not found")

When/what makes it necessary to use ctrl shift enter? only certain functions?

Also its best to narrow it down even if its a standard formula right? so it doesn't address millions of rows.

Also i tested it fully and it seems to work great now, thanks a lot for all the help.

 

[DanteAmor]

When/what makes it necessary to use ctrl shift enter? only certain functions?

An array formula is a type of formula that performs an operation on multiple values instead of a single value. The final result of an array formula can be either one item or an array of items, depending on how the formula is constructed.

To work correctly, array formulas need to be entered with control + shift + enter. When you enter with control + shift + enter, you'll see the formula wrapped in curly braces {} in the formula bar. Do not enter curly braces manually, or the formula won't work.

More info:

Guidelines and examples of array formulas - Microsoft Support

An array formula (one that spans multiple cells) can do calculations on rows and columns of cells where you might otherwise need to use several formulas. For example, you can count the number of characters that are contained in a range of cells, sum only numbers that meet certain conditions...

support.microsoft.com

-----------------------

Also i tested it fully and it seems to work great now, thanks a lot for all the help

Im glad to help you. Thanks for the feedback.

Regards
Dante Amor