Populate one column based the condition of two other columns

15.png
Attached is the file & snap shot for the problem kindly help if you can ASAP.

TABLE A
[table="width: 500, class: grid"]
[tr]
[td]Catagories state names devices
A4-100 HP A ALT
A4-101 MP B SLT
A4-102 AP C KTL
A4-103 ASSAM D XNO
A4-104 MP E PNO
A4-105 MUMBAI F FNO
A4-106 BENGAL G GNO
A4-107 MADRAS H SNO
A4-108 BENGAL I VNO
A4-109 KASHMIR J KNO
A4-110 PUNJAB K TNO
A4-111 HARYANA L BMO
A4-112 HARYANA M DPM
A4-113 BANGLORE N OIM
A4-114 PUNE O VBM
A4-115 KARNATAKA P SBM
______________________________________________
TABLE B
Catagories state names devices
A4-100 HP
A4-101 MP
A4-102 AP
A4-104 DELHI
A4-105 MUMBAI
A4-106 BENGAL
A4-107 MADRAS
A4-109 KASHMIR
A4-110 PUNJAB
A4-111 HARYANA
A4-112 UP
A4-113 BANGLORE

In above condition we want to have an automatic filling up of data in table B in column (name & device) but it should match first with state name with catagory codes of table B & then same with table A in order to avoid any wrong entry because in table A certain states are repeated but codes are unique so we want that filter should match two column of each table before filling data in table B

Hello warriorpoet7176 !

find attached !

formula used here are array formulas you need to hold control and shift together then hit enter to make them array formula instead of pressing control and enter

if this solves your query click " * " add rep icon to bottom left corner of my post !

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Dear Warriorpoet ! Thanks for the feedback and Rep. ! It's good to see I could help you !

Response and explanation is the part of ExcelForum......
By no means and ways I am a genius, there are many people in this community who solves tedious problems over a cup of coffee but still they say they are learner and when I come to this place I feel I have just started learning and there is much more to learn.

HATS OFF to to ExcelForum and ALL ADMINS/GURUS/Forum Moderators/Forum Experts and Everyone who is associated.

Have a look at Post#5 and follow the instruction given by Mr.Alansidman.