ISNA INDEX query

[marlhead]

Hi. I have a series of numbers in column B and then I have a further population of numbers in column C. Sometimes the numbers in column C will be present also in column B. Below is my formula which will give the answer "Required" for each number in column B that is new. Any duplicates will insert the actual number. (I've not worked out how to show a blank rather than the number)

=IF(ISNA(INDEX($B$1:B2,MATCH($B3,$B$1:B2,0),1)),"Required",INDEX($B$1:B2,MATCH($B3,$B$1:B2,0),1))

I am trying to write a similar formula for the numbers in column C but only to show "required" if the number is not present in column B or C further up the listing.

I hope this makes some sort of sense.

Any help out there would be appreciated

best regards

Marlhead

 

[texasalynn]

is this what you mean?

=IF(ISNA(AND(INDEX($B$1:B2,MATCH($B3,$B$1:B2,0),1),INDEX($C$1:C2,MATCH($B3,$C$1:C2,0),1))),
     "Required",INDEX($B$1:B2,MATCH($B3,$B$1:B2,0),1))

 

[marlhead]

Unfortunately that only works if the numbers in both cells for the row in question are all both populated further up the table of numbers. So if the number in column B is a new number but the one in column C is a duplicate then the result still shows as "required".

I believe I may have caused confusion. My original formula I will keep for working out the required numbers in column B but the second formula is to see if the number in column C has already been populated in both column B & C.

The logic you have used I think is definately going down the correct path but a little tweaking is still needed. I'll have a play around but hopefully my explanation above helps a little more.

 

[marlhead]

37185 Required
47125 Required
47103 37185 Required
47103 37189 Required
37182 47125 Required
37176 47126 Required Required
40012 Required

The above is how I would expect the result to show. By inserting "" after "required", I will therefore leave a blank rather than the original number. I hope this assists

 

[marlhead]

I've tried using the "OR" function rather than "AND". I can get each line to work but I am unable to combine the formula to work on both lines together.

 

[marlhead]

Re: INDEX MATCH query

Still no joy I'm afraid. I've changed the title of the thread to perhaps attract others to my problem. Any help would be greatly appreciated. Marlhead

 

[marlhead]

I believe I have cracked it by coming at it from a different angle

I use this formula to work out if the number in column "B" is required

=IF(AND(ISNA(VLOOKUP(B4,$B$1:B3,1,0)=B4),ISNA(VLOOKUP(B4,$C$1:C3,1,0)=B4)),"REQUIRED","")

then this one for column the number in column "C"

=IF(C4="","",IF(AND(ISNA(VLOOKUP(C4,$B$1:B3,1,0)=C4),ISNA(VLOOKUP(C4,$C$1:C3,1,0)=C4)),"REQUIRED",""))

Note column "B" is always populated but column "C" only on occasions.