vlookup or countif on 2 column match rather than one

gint32

Board Regular
Joined
Oct 8, 2014
Messages
139
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),"")
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
 
Upvote 0
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)))
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top