Multiple query criteria to filter cell value?

[Loki89]

Hi, so this is my question.

I need to create a formula/filter/script , that would look something in the likes of:
" =(E{range value} WHERE ((A{range value} & B{range value}) == (C{range value} & D{range value}))) "

" I need the functionality to assign a cell a value of another cell in another cell-range , where two other cell-ranges contain the same values as two other cell-ranges. "

Is that possible?
thanks

[Sindhus]

Check the attached sample. It may help you.

[Loki89]

Okay, so i solved it.
assuming you have headers to your columns:


a structure,something like:
________________________________
Sheet1= Sheet1
Col1(A1)="firstname"
Col2(B1)="lastname"
Col3(C1)="telephone number"

Sheet2=Sheet2
Col1(A1)="firstname"
Col2(B1)="lastname"
________________________________


Steps.........
________________________________
in Sheet1,Col4(D2):
=UPPER(CONCATENATE(A2," ",B2))
use the fill handler to drag the formula down the cells of the whole column to the end of neighbouring cells data.
then take the whole column and "copy" , "paste special -> values" , on itself.
then select all the data from cell A2 through to cell C? .
and give it a "Define Name" of "VALUESTOLOOKUPTHROUGH" .
________________________________
in Sheet2,Col3(C2):
=UPPER(CONCATENATE(A2," ",B2))
use the fill handler to drag the formula down the cells of the whole column to the end of neighbouring cells data.
Then take the whole column and "copy" , "paste special -> values" , on itself.
________________________________
in Sheet2,Col4(D2):
=IF(ISNA(VLOOKUP(C2,VALUESTOLOOKUPTHROUGH,3,FALSE)),"",VLOOKUP(C2,VALUESTOLOOKUPTHROUGHATA,3,FALSE))
use the fill handler to drag the formula down the cells of the whole column to the end of neighbouring cells data.
Then take the whole column and "copy" , "paste special -> values" , on itself.

helpful? tedious? maybe? working? definitely!



if anybody has an improvement on this let me know, thanks...

[Loki89]

thanks Sinhus, i chose to go the vlookup route.

I will post an example of my file soon.