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!

 

[DanteAmor]

Try this array formula.
The range to look for is from A1 to C100, you can change 100 for a larger row number, but consider not putting a number that is too large, since it is a array formula and can be slow. So the recommendation is up to the number of rows you have in the zCompany List and maybe a little more slack. So where you see 100 you change it to that row number.

varios 07oct2024.xlsm
	A	B	C	D
1			Mullinax
2
3
4			Mullinax Ford
5
Sheet1

Cell Formulas
Range	Formula
C4	C4	=INDEX('[zCompany List.xlsx]Contacts for Tool'!$A$1:$C$100, MAX((ISNUMBER(SEARCH($C$1,'[zCompany List.xlsx]Contacts for Tool'!$A$1:$C$100)))*ROW($A$1:$A$100)), MAX((ISNUMBER(SEARCH($C$1,'[zCompany List.xlsx]Contacts for Tool'!$A$1:$C$100)))*COLUMN($A$2:$C$2)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Bond00]

Got this message, maybe because I'm using 2016?

Edit, wait neverminded I got it to work.
But its showing up some wrong data from another customer from another row.
Also is this supposed to be highlighting ranges in my main doc?

=INDEX('[zCompany List.xlsx]Contacts for Tool'!$A$2:$C$500,MAX((ISNUMBER(SEARCH($C$1,'[zCompany List.xlsx]Contacts for Tool'!$A$2:$C$500)))*ROW($A$2:$A$500)),MAX((ISNUMBER(SEARCH($C$1,'[zCompany List.xlsx]Contacts for Tool'!$A$2:$C$500)))*COLUMN($A$2:$C$2)))

changed the A1 to A2 since the top row is the headers of the table all the data is in from the other sheet its pulling from.

 

[Bond00]

Also it pulls the wrong data, its pulling from Col 3 and not col 1 which is the rest of the company name. Also that's an address from somewhere else.

 

[Bond00]

Also keep in mind i use this formula in many cells to auto pull and fill in data just by changing 1 address # in the formula in 2 spots. It would search col A for the key words and then pull from the columns A through M in the COLUMNS($A$1:B1) part of the function.
And if i choose to type something in the search that isn't in the list it will just show it there
Also note, Col A is empty in this sheet im working within.

Q00001 Mew - New Network, Server and Services - Copy - Copy.xlsm
	B	C
1	Partial Search =	Test
2
3	SOLD TO:
4	Company Name	Test
5	Contact Name
6	Address
7	City State Zip
8	Country
9
10	Phone
11	Fax
12	Email
Info for Quote

Cell Formulas
Range	Formula
C4	C4	=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)
C5	C5	=IF(ISNUMBER(SEARCH("",VLOOKUP($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A$2:$L$1500,COLUMNS($A$1:B1),0))),VLOOKUP($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A$2:$L$1500,COLUMNS($A$1:B1),0),"")
C6	C6	=IF(ISNUMBER(SEARCH("",VLOOKUP($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A$2:$L$1500,COLUMNS($A$1:C1),0))),VLOOKUP($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A$2:$L$1500,COLUMNS($A$1:C1),0),"")
C7	C7	=IF(ISNUMBER(SEARCH("",VLOOKUP($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A$2:$L$1500,COLUMNS($A$1:D1),0))),VLOOKUP($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A$2:$L$1500,COLUMNS($A$1:D1),0),"")
C8	C8	=IF(ISNUMBER(SEARCH("",VLOOKUP($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A$2:$L$1500,COLUMNS($A$1:E1),0))),VLOOKUP($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A$2:$L$1500,COLUMNS($A$1:E1),0),"")
C10	C10	=IF(ISNUMBER(SEARCH("",VLOOKUP($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A$2:$L$1500,COLUMNS($A$1:G1),0))),VLOOKUP($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A$2:$L$1500,COLUMNS($A$1:G1),0),"")
C11	C11	=IF(ISNUMBER(SEARCH("",VLOOKUP($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A$2:$L$1500,COLUMNS($A$1:H1),0))),VLOOKUP($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A$2:$L$1500,COLUMNS($A$1:H1),0),"")
C12	C12	=IF(ISNUMBER(SEARCH("",VLOOKUP($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A$2:$L$1500,COLUMNS($A$1:I1),0))),VLOOKUP($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A$2:$L$1500,COLUMNS($A$1:I1),0),"")

 

[DanteAmor]

changed the A1 to A2 since the top row is the headers

Don't change it, that's why you get another result.

 

[DanteAmor]

If you don't want to put A1, then you should subtract the row number where your data starts + 1

Dante Amor
	A	B	C	D
1			mulli
2
3
4			Mullinax Ford
5
Sheet1

Cell Formulas
Range	Formula
C4	C4	=INDEX('[zCompany List.xlsx]Contacts for Tool'!$A$2:$C$500, MAX((ISNUMBER(SEARCH($C$1,'[zCompany List.xlsx]Contacts for Tool'!$A$2:$C$500)))*ROW($A$2:$A$500))-ROW($A$2)+1, MAX((ISNUMBER(SEARCH($C$1,'[zCompany List.xlsx]Contacts for Tool'!$A$2:$C$500)))*COLUMN($A$2:$C$2)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Bond00]

not sure why i get this result, i pasted your function in above without any changes. I just copy pasted it right in the cell in C4

When i copy in the code you sent in post #2 (no changes) it gave me info back from C3 in the zCompany List doc. and the Mulli name is on row 145 so it should have returned me A145 value

this is the full list of data it pulls from in the other sheet.

 

[DanteAmor]

Don't forget to enter the formula with Shift + Control + Enter (Not just Enter)

 

[Bond00]

Don't forget to enter the formula with Shift + Control + Enter (Not just Enter)

oh ok, why what does that do? never knew that.