vlookup or countif on 2 column match rather than one

gint32

Board Regular
Joined
Oct 8, 2014
Messages
120
Hi, does anyone know of a way to use a vlookup or countif on a picklist that displays two columns, in a picklist as I want to be abe to pick from a list that containes surnames in one column and first name on the next as the list i have has alot of names the has for example: Smith , adam, brian charles,

using a combination of data validation but to display the content of to columns/rows instead of one and

=IF(COUNTIF(AllCompanies,$A3)<>0,VLOOKUP('Data Sheet'!$A3,AllData,3,FALSE),"")
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

gint32

Board Regular
Joined
Oct 8, 2014
Messages
120
I take it this question is way too complicated for this forum
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
I take it this question is way too complicated for this forum

Perhaps, such cannot be excluded. Meanwhile, you could post small representative amples along with the result(s) that you want to see to obtain.
 

gint32

Board Regular
Joined
Oct 8, 2014
Messages
120
SName
FName
Cust_ID
Address
Smith
Adam
J02135698
2/a Main St
Smith
John
L14545879
14 Bank St
Smith
Mary
A3615478
11 Park Av
Jones
Tom
B2463800
2 Sleepy St


<tbody>
</tbody>
Basically, I am looking to be able to select from a pick list from column 1, then in column 2's pick list will only show matches that have the surname "Smith" in Col1 eg:Adam, John and mary only, So basically each column needs to narrow down till we have the correct client...Hope this makes more sense then the previous post
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192

ADVERTISEMENT

SName
FName

Cust_ID
Address
Smith
Adam
J02135698
2/a Main St
Smith
John
L14545879
14 Bank St
Smith
Mary
A3615478
11 Park Av
Jones
Tom
B2463800
2 Sleepy St

<TBODY>
</TBODY>
Basically, I am looking to be able to select from a pick list from column 1, then in column 2's pick list will only show matches that have the surname "Smith" in Col1 eg:Adam, John and mary only, So basically each column needs to narrow down till we have the correct client...Hope this makes more sense then the previous post

Let A:D of Sheet1 house the sample you posted, the headers included.

Let A1 of Sheet2 house a test value (condition) like Smith...

A2: Idx; B2: Fname; C2: Cust_ID; and D2: Address

A3, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IFERROR(SMALL(IF(Sheet1!$A$2:$A$100=$A$1,
  ROW(Sheet1!$A$2:$A$100)-ROW(Sheet1!$A$2)+1),ROWS($A$3:A3)),"")

B3, just enter, copy across, and down:
Rich (BB code):
=IF($A3="","",INDEX(Sheet1!$A$2:$D$100,$A3,MATCH(B$2,Sheet1!$A$1:$D$1,0)))
 

juweh

Board Regular
Joined
Nov 1, 2014
Messages
55
I am not sure if i got the question right...

column A = list of last name, after you select a last name (example: smith) on
column B = it will provide you the list of Fname with "Smith" as their last name then on
column C = it will provide the list of cust_id then on
column D = the address
 

gint32

Board Regular
Joined
Oct 8, 2014
Messages
120

ADVERTISEMENT

I am not sure if i got the question right...

column A = list of last name, after you select a last name (example: smith) on
column B = it will provide you the list of Fname with "Smith" as their last name then on
column C = it will provide the list of cust_id then on
column D = the address

Yep, thats correct but each from individual pick lists for the first X3 columns(stops at CustID)..It would be really easy to do in MS Access but I have no clue with excel...thanks
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Yep, thats correct but each from individual pick lists for the first X3 columns(stops at CustID)..It would be really easy to do in MS Access but I have no clue with excel...thanks

Heve you looked at post #3 at all? Doesn't that cover your intent? If not, try to show what be the results, based on the sample you posted?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,809
Messages
5,525,002
Members
409,615
Latest member
papaluigi94

This Week's Hot Topics

Top