# Returning an ID# given two criteria

#### uceaamh

##### New Member
Hello,

I've been trying to write a formula to return an ID based on two criteria: 1. a unique premises ID, and 2. category type. The data sits on one sheet, the return info on another.

It looks like this:

DATA (In Table2):

 PIN PINStatus PINTYPE PREMISESID 01 Revoked PON 123 02 Active HIPON 223 03 Active MIPON 233 04 Revoked HIPON 123 05 Active PON 243 06 Revoked MIPON 123

<tbody>
</tbody>

Trying to organize it, in a different sheet, like so:

 Premises ID PON PON# HIPON HIPON# MIPON MIPON# 123 Y 01 Y 04 Y 06

<tbody>
</tbody>

I have this:

=IF(AND((INDEX(Table2[PremisesID],(MATCH(A13,Table2[PremisesID],0))))=A13,(INDEX(Table2[PINType],(MATCH(Table1[PON],Table2[PINTYPE]))))=Table1[PON]),LEFT(Table2[PIN)],999),"N")

Which has returned results, but the wrong ones.

Any help would be appreciated.

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### Chrisdontm

##### Well-known Member
From the example of your desired results it looks like you are calling that Table: Table1
and it looks like it starts in cell A12.

Below are six formulas to put in each column to the right of A13
Let me know if I am not understanding you right.

In cell B13 try;

In cell C13 try;

In cell D13 try;

In cell E13 try;

In cell F13 try;

In cell G13 try;

#### uceaamh

##### New Member
Chrisdontm,

That worked. Thanks alot! Sorry if that explanation wasn't totally clear - I was struggling to explain to myself what I needed done, so the initial post wasn't great.

Now I'm running into a second problem. This spreadsheet is tracking notes (3 different types of note, PON, HIPON, and MIPON, each with their own identifier - the #) against a particular location (Premises ID). Sometimes one location can have multiple instances of one type of note. Using SUMPRODUCT now gives me the sum of these notes identifying #'s.

For example:

 PIN PINStatus PINTYPE PREMISESID 01 Revoked PON 123 02 Active HIPON 223 03 Active MIPON 233 04 Revoked HIPON 223 05 Active PON 243 06 Revoked MIPON 123

<tbody>
</tbody>

Where HIPON 02 and 04 are both on the same Premises (223) would give the result:

 Premises ID PON PON# HIPON HIPON# MIPON MIPON# 223 N N Y 06 N N

<tbody>
</tbody>

Any suggestions how to tackle that problem?

Thanks again

#### Chrisdontm

##### Well-known Member
It seems like I didn't get an email response...???

Anyway;
So how many possible answers would you need in any one cell?

#### uceaamh

##### New Member
Ideally we need just the one - when there are multiples of one notice type on one premises, someone made a mistake.

I've seen up to 3 notices of the same type on one premises as maximum. I guess to be safe I'd want to be able to record up to 5 answers per cell.

Replies
2
Views
48
Replies
7
Views
303
Replies
6
Views
436
Replies
17
Views
6K
Replies
8
Views
541

1,171,832
Messages
5,877,817
Members
433,288
Latest member
Kietkiengiang

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back