View original (French)

Search V with two criteria

seb58470 -  
pépé35530 Posted messages 2951 Registration date   Status Membre Last intervention   -
Hello,

I would like to perform a search with two criteria but I haven't found any answers on your site or in other forums.
For example: Searching for a person's address based on the last name AND first name.
I tried using the Index+ equi function without any results.

Thank you for your responses.
Best regards,
Sébastien CLATOT

Configuration: Windows XP / Internet Explorer 8.0

3 réponses

Réponse 1 / 3
tontong Posted messages 2575 Registration date   Status Membre Last intervention   1 064
 
Hello,
The last names in A, the first names in B, the addresses in C
3 solutions:
1 Create an auxiliary column D with D2=A2&B2. Copy down. Use INDEX/MATCH to find the address of "DupontJean" using column D.
2 Use an array formula =INDEX($C$2:$C$100;MATCH(1;($A$2:$A$100="Dupont")*($B$2:$B$100="Jean");0))
Confirm with CTRL+SHIFT+ENTER instead of the usual ENTER.
3 Use a "database" formula
Name the fields Last Name First Name Address
Create a criteria range G1= Last Name G2=Dupont H1=First Name H2=Jean
Enter the formula =DGET(A1:C100;"Address";G1:H2)
1
Réponse 2 / 3
pépé35530 Posted messages 2951 Registration date   Status Membre Last intervention   1 390
 
Hello,

I need to have roughly the same solution:

Add a column to concatenate the first name and last name (G in my sheet):

=+B2&" "&C2

Last names in B and first names in C

copy the formula through the entire column

In the search area:

Either enter the searched last name and first name in two separate cells or enter the searched last name and first name directly with a space in between.
If last name and first name are separated: concatenate the last name and first name (in my sheet)

In A6:

=B6&" "&C6

In A8 enter the formula to search in column G:

=VLOOKUP(A6;G2:G4;1;FALSE)

In B8 search for the row number of the matrix:

=VLOOKUP(A6;G2:G4;1;FALSE)

In B8 find the row corresponding to the name found in A8

=MATCH(A8;G2:G4)

In row 10, extract the different elements of the address:

in B10: the last name

=INDEX($B$2:$F$4;$B$8;1)

B2:F4 represents my matrix

Copy the formula in B10 to the right to display
the different elements of the address, indicating each time the column number in the matrix, for the next cell:

=INDEX($B$2:$F$4;$B$8;2)

Note: it is preferable to name the matrices.
To avoid receiving error messages when the cells containing the last name or first name are empty, we can add a test with the IF function in the formulas.

Best,

pépé
0
Réponse 3 / 3
pépé35530 Posted messages 2951 Registration date   Status Membre Last intervention   1 390
 
Good evening,

A simpler solution:

The database is in A2:F4
Column A indicates an order number 1, 2, 3, etc...
Column B contains the last names, column C the first names, column D the address, column E the postal code, and column F the city;

We enter the last name and first name we are looking for in B6 and C6.

In B13 enter the following formula (to be adapted)

=SUMPRODUCT((B2:B4=B6)*(C2:C4=C6)*(A2:A4))

This gives the number of the searched row

In line 14

=INDEX($A$2:$F$4,$B$13,2)
displays the searched last name (2nd column)

Copy to the right while modifying the column number to display the other elements of the address.

To avoid error messages, we can modify the formulas:
B13 becomes:

=IF(OR(B6="",C6=""),"",SUMPRODUCT((B2:B4=B6)*(C2:C4=C6)*(A2:A4)))

B14:

=IF($B$13="",INDEX($A$2:$F$4,$B$13,2))

etc...

Best,

pépé
0