Help on Lookup formula

[dracron]

Im stuck!
What I am trying to do:
If cells from spreadsheet1: C1 and E1, match cells on spreadsheet2:C1 and D1 it will show spreadsheet2s data from cell E1.

Thank you in advanced for your help.

 

[dracron]

1. To be more specific:

Spreadsheet1: C1(name) and E1(state)
Spreadsheet2: C1(name) and D1(state)
Spreadsheet2: E1(Lic #)

 

[alexanders]

=IF(AND(C1=spreadsheet2!C1,E1=spreadsheet2!D1),spreadsheet2!E1,)

 

[dracron]

Thank you for your help, but it didnt work, and I am not sure why. It just gives me a false when I know there a many that match.

 

[OTOTO]

=IF(AND($C1=Sheet2!$C1,$E1=Sheet2!$D1),Sheet2!$E1,0)

 

[dracron]

Thanks OTOTO but the same thing happens just instead of false there is now a 0. Not sure why this is happening as I know C1 should return a result.
I want the data from E1 to pull over.

 

[OTOTO]

Thanks OTOTO but the same thing happens just instead of false there is now a 0. Not sure why this is happening as I know C1 should return a result.
I want the data from E1 to pull over.

I threw the formula into a test spreadsheet and didn't have a problem.
are the cell values in the same format?
did you rename the sheets?
double check the column names?
is there another way (create a third sheet with information from both sheets and then write the formula, this way you will not have the sheet references and you can easily confirm formatting problems??)

 

[dracron]

Yeah the format is the same and sheet name etc. Instead of individual cells can it easily be changed so instead of C1 it can look at C:C? and match that way?
at least that way if its not in a matching order it will provide a result.
Sort of like a vlookup...

Thanks

 

[Aladin Akyurek]

Im stuck!
What I am trying to do:
If cells from spreadsheet1: C1 and E1, match cells on spreadsheet2:C1 and D1 it will show spreadsheet2s data from cell E1.

Thank you in advanced for your help.

=INDEX(Sheet2!$E$1:$E$100,MATCH(1,IF(Sheet2!$C$1:$C$100=$C1,IF(Sheet2!$D$1:$D$100=$E1,1)),0))

which needs to be confirmed with control+shift+enter, not just enter.

Is this what you have in mind?