Searching another sheet for values that meet required specification

[j0rgeo]

Hi all - first post!

Not sure how to word what I want but here goes. I have an excel spreadsheet that on one sheet records details of invoices and on another records authorisations to pay invoices. I want a cell to automatically search the Authorisation sheet for a row and ideally a corresponding Authorisation ID that matches the invoice.

Unique ID <tbody> </tbody>	Vendor	Client	Start Date	Existing Auth? (should say)
#001	Bob's Burgers	Joe Bloggs	9/1/01	No
#002	Fred's Flying	Homer Simpson	2/5/01	Yes
#003	Bob's Burgers	Katie Colon	2/2/02	Yes

<tbody>
</tbody>

Auth. ID	Vendor	Client	Auth. Start	Auth End
J001	Fred's Flying	Frank Sinatra	03/04/05	20/09/05
J002	Bob's Burgers	Katie Colon	01/01/01	31/05/05
J003	Fred's Flying	Homer Simpson	01/05/01	03/05/01

<tbody>
</tbody>

Existing authorisation column should be yes if:
Vendor matches
Client matches Start date between authorised start and end periods.

For this I used =if(B2=authB2,B3=authB3,authB4<=B4<=authB5),"Yes","No")

Which I think is sound for the auth check but I'm not sure how to use it to search for a row that matches and not get stuck on the first column that meets the criteria.

Many thanks for any advice incoming! Please let me know if I'm not being clear.

 

[jarjarbingie]

Hi all - first post!

Not sure how to word what I want but here goes. I have an excel spreadsheet that on one sheet records details of invoices and on another records authorisations to pay invoices. I want a cell to automatically search the Authorisation sheet for a row and ideally a corresponding Authorisation ID that matches the invoice.

Unique ID <tbody> </tbody>	Vendor	Client	Start Date	Existing Auth? (should say)
#001	Bob's Burgers	Joe Bloggs	9/1/01	No
#002	Fred's Flying	Homer Simpson	2/5/01	Yes
#003	Bob's Burgers	Katie Colon	2/2/02	Yes

<tbody>
</tbody>

Auth. ID	Vendor	Client	Auth. Start	Auth End
J001	Fred's Flying	Frank Sinatra	03/04/05	20/09/05
J002	Bob's Burgers	Katie Colon	01/01/01	31/05/05
J003	Fred's Flying	Homer Simpson	01/05/01	03/05/01

<tbody>
</tbody>

Existing authorisation column should be yes if:
Vendor matches
Client matches Start date between authorised start and end periods.

For this I used =if(B2=authB2,B3=authB3,authB4<=B4<=authB5),"Yes","No")

Which I think is sound for the auth check but I'm not sure how to use it to search for a row that matches and not get stuck on the first column that meets the criteria.

Many thanks for any advice incoming! Please let me know if I'm not being clear.

assuming table 1 is in Sheet1 and table 2 is in Sheet2:

E2=IF(SUM(IF((Sheet2!$B$2:$B$4=$B2)*(Sheet2!$C$2:$C$4=$C2)*(Sheet2!$D$2:$D$4<=$D2)*(Sheet2!$E$2:$E$4>=$D2),1,0))>0,"Yes","No") press CTRL+SHIFT+ENTER

 

[j0rgeo]

Thanks for this, it works well.

Is there a way of pulling through the Auth. ID from the row that meets the criteria? Maybe something like:

E2=IF(​logical statements,"Yes - Auth ID","No")

 

[jarjarbingie]

Thanks for this, it works well. Is there a way of pulling through the Auth. ID from the row that meets the criteria? Maybe something like:E2=IF(​logical statements,"Yes - Auth ID","No")

E2=INDEX(Sheet2!$A$2:$A$4, MIN(IF((Sheet2!$B$2:$B$4=$B2)*(Sheet2!$C$2:$C$4=$C2)*(Sheet2!$D$2:$D$4<=$D2)*(Sheet2!$E$2:$E$4>=$D2), ROW(Sheet2!$A$2:$A$4), ""))-1)press CTRL+SHIFT+ENTER