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]

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.

 

[Bond00]

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.

ok thats pretty interesting.. never knew that. and thats with the index function or any array function like that?

Also it works now, but how do i put it in the other cells now to pull data back from the other columns for cells in this sheet C5, C6, C7 etc?

Also this works kind of funny, if i type North in the search it brings up S. North America before bringing up Northwest. So it brings up something that starts with a S before N Vlookup just went in alphabetical order which was kinda nice. I typed Sa in search and it brings up some guys name that doesn't even have "sa" in his name. So it still seems its not really working fully. I'd like it to prioritize Col A and if nothing there then go to col B and then search Col C if nothing matched before.
It seems to be pulling searches from col C first and then if i type more then it gets to col A's search match.

Also it just brings in whatever it finds, I need it to bring in the data in a row based on a search it finds. So example. If it finds a match in col B I need a way to bring in data from that whole row of that match. So if it finds the name Sally in col C17 I need to bring in data from that whole row, A17:M17 and be able to put the data from A17 into C4 in this sheet, and C17 into C5 etc.

this works like that, =VLOOKUP($E$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A$2:$L$1500,COLUMNS($A$1:A1),0)
It searchs only col A in that doc and then i can bring in col A:M and put it into the correct cell i need it to go in. Like i on post #5

Also side note, just curious for learning purposes, is using index better/more efficient than using SEARCH and VLOOKUP functions like i had before?

 

[DanteAmor]

So example. If it finds a match in col B I need a way to bring in data from that whole row of that match. So if it finds the name Sally in col C17 I need to bring in data from that whole row, A17:M17 and be able to put the data from A17 into C4 in this sheet, and C17 into C5 etc.

In that case it is simpler.

In cell C4 the formula will put the value of column A or B or C.

In cell J3 or wherever you want, with that formula you will obtain the row number where the name obtained in formula C4 is located.

For example:

varios 07oct2024.xlsm
	A	B	C	D	E	F	G	H	I	J
1			mull						Row:	3
2
3
4		Company Name	Mullinax es
5		Contact Name	John
6		Address	45, main st
Sheet1

Cell Formulas
Range	Formula
J1	J1	=IFERROR(MATCH(C4,'[zCompany List.xlsx]Contacts for Tool'!$A:$A,0),IFERROR(MATCH(C4,'[zCompany List.xlsx]Contacts for Tool'!$B:$B,0),IFERROR(MATCH(C4,'[zCompany List.xlsx]Contacts for Tool'!$A:$A,0),0)))
C4	C4	=IFERROR(VLOOKUP(C1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A:$A,1,0),IFERROR(VLOOKUP(C1&"*",'[zCompany List.xlsx]Contacts for Tool'!$B:$B,1,0),IFERROR(VLOOKUP(C1&"*",'[zCompany List.xlsx]Contacts for Tool'!$C:$C,1,0),"Not exists")))
C5	C5	=INDIRECT("'[zCompany List.xlsx]Contacts for Tool'!$D$" & $J$1)
C6	C6	=INDIRECT("'[zCompany List.xlsx]Contacts for Tool'!$E$" & $J$1)

 

[DanteAmor]

In cell J1 or wherever you want, with that formula you will obtain the row number where the name obtained in formula C4 is located.

I meant J1

 

[DanteAmor]

Or this option, without volatile INDIRECT function:

varios 07oct2024.xlsm
	A	B	C	D	E
1			mull
2
3
4		Company Name	Mullinax es
5		Contact Name	John
6		Address	45, main st
7
Sheet1

Cell Formulas
Range	Formula
C4	C4	=IFERROR(VLOOKUP(C1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A:$A,1,0),IFERROR(VLOOKUP(C1&"*",'[zCompany List.xlsx]Contacts for Tool'!$B:$B,1,0),IFERROR(VLOOKUP(C1&"*",'[zCompany List.xlsx]Contacts for Tool'!$C:$C,1,0),"Not exists")))
C5	C5	=IFERROR(VLOOKUP($C$4,'[zCompany List.xlsx]Contacts for Tool'!$A:$D,4,0),IFERROR(VLOOKUP($C$4,'[zCompany List.xlsx]Contacts for Tool'!$B:$D,3,0),IFERROR(VLOOKUP($C$4,'[zCompany List.xlsx]Contacts for Tool'!$C:$D,2,0),"")))
C6	C6	=IFERROR(VLOOKUP($C$4,'[zCompany List.xlsx]Contacts for Tool'!$A:$E,5,0),IFERROR(VLOOKUP($C$4,'[zCompany List.xlsx]Contacts for Tool'!$B:$E,4,0),IFERROR(VLOOKUP($C$4,'[zCompany List.xlsx]Contacts for Tool'!$C:$E,3,0),"")))

 

[Bond00]

In that case it is simpler.

In cell C4 the formula will put the value of column A or B or C.

I dont want the column, i just need that row A:M of the matched search. The formula i had before worked good but the main thing i wanted was to be able to have more search possibilities instead of just the company name in col A. col B is the acronym or short name and col C is the contact name for that company. So searching between those 3 options i want to then bring in that row's data.
So if my search finds anything in A8:C8 that matches I would then be able to reference all the data between A8:M8 based on that search match.

"Or this option, without volatile INDIRECT function:"
Whats a better method? dealing with the volatile function or more references back to another excel doc over the network?

 

[Bond00]

Or this option, without volatile INDIRECT function:

varios 07oct2024.xlsm
	A	B	C	D	E
1			mull
2
3
4		Company Name	Mullinax es
5		Contact Name	John
6		Address	45, main st
7
Sheet1

Cell Formulas
Range	Formula
C4	C4	=IFERROR(VLOOKUP(C1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A:$A,1,0),IFERROR(VLOOKUP(C1&"*",'[zCompany List.xlsx]Contacts for Tool'!$B:$B,1,0),IFERROR(VLOOKUP(C1&"*",'[zCompany List.xlsx]Contacts for Tool'!$C:$C,1,0),"Not exists")))
C5	C5	=IFERROR(VLOOKUP($C$4,'[zCompany List.xlsx]Contacts for Tool'!$A:$D,4,0),IFERROR(VLOOKUP($C$4,'[zCompany List.xlsx]Contacts for Tool'!$B:$D,3,0),IFERROR(VLOOKUP($C$4,'[zCompany List.xlsx]Contacts for Tool'!$C:$D,2,0),"")))
C6	C6	=IFERROR(VLOOKUP($C$4,'[zCompany List.xlsx]Contacts for Tool'!$A:$E,5,0),IFERROR(VLOOKUP($C$4,'[zCompany List.xlsx]Contacts for Tool'!$B:$E,4,0),IFERROR(VLOOKUP($C$4,'[zCompany List.xlsx]Contacts for Tool'!$C:$E,3,0),"")))

this worked good except for whatever i search goes into C4, i need the company name to go in C4 which would be in the A col on the zCompany list doc
Right now if i search a name from C col that is then put into C4 on main doc. I need the A col company name to go into C4

=IFERROR(VLOOKUP($C$4,'[zCompany List.xlsx]Contacts for Tool'!$A:$C,3,0),IFERROR(VLOOKUP($C$4,'[zCompany List.xlsx]Contacts for Tool'!$B:$C,2,0),IFERROR(VLOOKUP($C$4,'[zCompany List.xlsx]Contacts for Tool'!$C:$C,1,0),"")))
=IFERROR(VLOOKUP($C$4,'[zCompany List.xlsx]Contacts for Tool'!$A:$D,4,0),IFERROR(VLOOKUP($C$4,'[zCompany List.xlsx]Contacts for Tool'!$B:$D,3,0),IFERROR(VLOOKUP($C$4,'[zCompany List.xlsx]Contacts for Tool'!$C:$D,2,0),"")))

Also can you walk me through this so i understand better how to use it and how it works.
I notice the pattern that Tool'!$A:$D,4,0) and Tool'!$B:$D,3,0) and Tool'!$C:$D,2,0), go down 1 from left to right in the function (not sure why though), and also they go up 1 with each new reference to the next cell in the row, both for the letter/col and number/row. But not sure why.

I think vlookup seems to work good for my needs on this but i looked up some stuff and seems match with index would have worked good too.
"MATCH to find the searched value in a range, then use INDEX to find the other values in the corresponding columns"

 

[Bond00]

Also if its better and computationally less taxing on the sheet I don't mind bringing in the whole A:M row of the search into this sheet on row A55:M55 and then referencing it from there. In this use case it wouldn't matter doing that if its better.

 

[Bond00]

Also since C4 should always be the company name from A col all other functions can be shorter referencing it, so thats nice.

=IFERROR(VLOOKUP($C$4,'[zCompany List.xlsx]Contacts for Tool'!$A:$C,3,0),"")

just need that first function in C4 to be fixed so it works right.

 

[DanteAmor]

Also can you walk me through this so i understand better how to use it and how it works.

In my example I am assuming how you have the zCompany List data, but my formulas would be more precise if you give a complete and clear example of what you have in the zCompany List and what you expect as a result.
Use XL2BB tool to provide examples, you do not need to provide real data, you can provide dummy data.

Note XL2BB:​

For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.​

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in​

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.​

just need that first function in C4 to be fixed so it works right

Try:

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

Cell Formulas
Range	Formula
C4	C4	=IFERROR(INDEX('[zCompany List.xlsx]Contacts for Tool'!$A:$A, IFERROR(MATCH($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$A:$A,0), IFERROR(MATCH($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$B:$B,0), IFERROR(MATCH($C$1&"*",'[zCompany List.xlsx]Contacts for Tool'!$C:$C,0),"")))),"Not found")