Index/Match with multiple sheets

[Tripacerchick]

I have a working version of this problem that has everything on one sheet, but it is getting to cumbersome and difficult to track specific items that I need. So, I've split up my lines of business between different tabs, but I still need to be able to lookup specific data when I type in a Unique ID. In the attached example, when I type in the Unique ID on the COMM tab, I need it to look for that Unique ID on the GRP tab and populate the Insured Name. If it doesn't find the Unique ID on the GRP tab, it needs to then look at the O65 tab, etc etc.

My first attempt at the logic of it, trying to use an IF(AND... formula does not work. I'm not sure what I am doing wrong. Any help with this one? Is this even possible?

[FDibbins]

Not really sure why you have used AND there? You normally only use AND (and OR) if you are testing more than 1 thing.

Why do you think having it all on 1 sheet is cumbersome? That is the generally accepted way of data storage/entry/capture in excel. You then have other sheets that would do any extracting, analysis, summaries etc.

In your case, as long as you have a unique ID, finding what you want should not be a problem. even in a long list - there are a multitude of tools available to help with this.

As far as your formula is concerned...
=IFERROR(INDEX(GRP!B:B,MATCH(Comm!A2,GRP!A:A,0)),IFERROR(INDEX('O65'!B:B,MATCH(Comm!A2,'O65'!A:A,0)),IFERROR(INDEX('U65'!B:B,MATCH(Comm!A2,'U65'!A:A,0)),"Not Found")))

[sandy666]

Use PowerQuery aka Get&Transform (you've 365 so this feature is built-in)

steps:
append all source tables
merge with "search" table
set value in A2
use Ctrl+Alt+F5 to refresh query table

[Tripacerchick]

Thanks, FDibbins. Keeping everything on one sheet does work better for what I'm trying to do on the COMM tab, but when I have to manipulate the data on the main sheet, the formulas tend to get wonky when I have to resort. Not sure why the formulas don't stay with the row I'm resorting. Plus, having multiple tabs allows me to look at just the data I want without having to filter.

Thanks for the solution. It works great! Since I'm using this to build a report for our accountant to pay commissions on, I need to be able to copy the formula down and this does exactly what I need it to.

[Tripacerchick]

sandy666, is that something that will work across multiple columns and multiple rows? What I'm ultimately trying to do is create a report for our account so she can pay our commissions out to our agents. On the example, I only included one match column (insured name) but I have about 6 that I need to "auto fill". And each row is another account to pay commission on.

[sandy666]

so attach representative example excel file

[beepetark]

I have a working version of this problem that has everything on one sheet, but it is getting to cumbersome and difficult to track specific items that I need. So, I've split up my lines of business between different tabs, but I still need to be able to lookup specific data when I type in a Unique ID. In the attached example, when I type in the Unique ID on the COMM tab, I need it to look for that Unique ID on the GRP tab and populate the Insured Name. If it doesn't find the Unique ID on the GRP tab, it needs to then look at the O65 tab, etc etc.

My first attempt at the logic of it, trying to use an IF(AND... formula does not work. I'm not sure what I am doing wrong. Any help with this one? Is this even possible?

Keep one cell for (definevalue) i.e., at A1
1st define name for corresponding tables, and
=Vlookup(lookup value, indirect(definename),2,0)
Give ur mail id

[beepetark]

Mail me sir, beepetark At gmail dot com

[azumi]

Put this on B2 on sheet "Comm"
=IFERROR(VLOOKUP(A2,INDIRECT("'"&LOOKUP(9,99999999999999E+307,1/COUNTIF(INDIRECT("'"&{"GRP","O65","U65"}&"'!A2:A1000"),A2),
{"GRP","O65","U65"})&"'!A2:B1000"),2,0),"")