Please help me with the below conditions. I need to fulfill 2 different conditions from two cells and then give me a correspoding value.

Workbook 1(Aset mov form) has the data. Workbook 2 (Floor map)should be updated based on workbook.

In workbook 2, i need the following:

Vlookup for deskno in workbook 1, if it is present, then the service line, cell next to deskno should match with the service line in workbook 1. If these both conditions match, then it should give the name corresponding to that match. I tried the below formula.

=IF(AND(H18=VLOOKUP(H18,'[Asset_movm_form.xlsx]Asset movement details'!$H:$H,1,0),H16=VLOOKUP(H16,'[Asset_movm_form.xlsx]Asset movement details'!$I:$I,1,0)),VLOOKUP(H18,'[Asset_movm_form.xlsx]Asset movement details'!$H:$M,6,0)). Though this fulfills my purpose, i am looking for cell specific values rather than the entire column for service line column.

Please suggest friends..

Attached Files

	Floor_map.xlsx (11.6 KB, 59 views)
	Asset_movm_form.xlsx (12.4 KB, 50 views)

Hi David.

Seems quite simple, I would manage this by using isna() as a test, so if your first condition is false it stops.
Condition 1 does the desk exist
Condition 2 does the service line match.

=IF(ISNA(VLOOKUP(E12,'[Copy of Asset_movm_form.xlsx]Asset movement details'!$H$6:$I$27,1,FALSE))=TRUE,"No Desk",IF(VLOOKUP(E12,'[Copy of Asset_movm_form.xlsx]Asset movement details'!$H$6:$I$27,2,FALSE)=E10,VLOOKUP(E12,'[Copy of Asset_movm_form.xlsx]Asset movement details'!$H$6:$M$27,6,FALSE),"Fail"))

Clearly you can change bits and pieces to suit.

Hope it helps
Matthew

Thank you mathews. I used similar thing and it worked. This is the formula that i used.

=IF(AND(H18=VLOOKUP(H18,'[Asset_movm_form.xlsx]Asset movement details'!$H:$H,1,0),H16=VLOOKUP(H18,'[Asset_movm_form.xlsx]Asset movement details'!$H:$I,2,0)),VLOOKUP(H18,'[Asset_movm_form.xlsx]Asset movement details'!$H:$M,6,0))

But now to look more better, i am trying to use Iferror function to eliminate the false values. I am not sure if this works or not, but is there a possibility that when the value is false, the cell turns to a different color? let me know your thought.

Again thanks so much for your time and suggestion. I could learn a new formula.

regards,
david