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.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]

Unique ID

<tbody>
</tbody>

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Auth. ID[/TD]
[TD]Vendor[/TD]
[TD]Client[/TD]
[TD]Auth. Start[/TD]
[TD]Auth End[/TD]
[/TR]
[TR]
[TD]J001[/TD]
[TD]Fred's Flying[/TD]
[TD]Frank Sinatra[/TD]
[TD]03/04/05[/TD]
[TD]20/09/05[/TD]
[/TR]
[TR]
[TD]J002[/TD]
[TD]Bob's Burgers[/TD]
[TD]Katie Colon[/TD]
[TD]01/01/01[/TD]
[TD]31/05/05[/TD]
[/TR]
[TR]
[TD]J003[/TD]
[TD]Fred's Flying[/TD]
[TD]Homer Simpson[/TD]
[TD]01/05/01[/TD]
[TD]03/05/01[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]

Unique ID

<tbody>
</tbody>

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]Auth. ID[/TD]
[TD]Vendor[/TD]
[TD]Client[/TD]
[TD]Auth. Start[/TD]
[TD]Auth End[/TD]
[/TR]
[TR]
[TD]J001[/TD]
[TD]Fred's Flying[/TD]
[TD]Frank Sinatra[/TD]
[TD]03/04/05[/TD]
[TD]20/09/05[/TD]
[/TR]
[TR]
[TD]J002[/TD]
[TD]Bob's Burgers[/TD]
[TD]Katie Colon[/TD]
[TD]01/01/01[/TD]
[TD]31/05/05[/TD]
[/TR]
[TR]
[TD]J003[/TD]
[TD]Fred's Flying[/TD]
[TD]Homer Simpson[/TD]
[TD]01/05/01[/TD]
[TD]03/05/01[/TD]
[/TR]
</tbody>[/TABLE]

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