How to return a random cell value based on a condition

[Gracy0101]

I am looking for a help with a formula in excel. I have two tabs in my excel
Updates:

One:

My input is in cell F1

When the input is 1, it should check if ID 1 is available in "updates" sheet and return a random "Code" from "updates Sheet" which has ID 1.

IF ID 1 is not available in Updates sheet, it should check in sheet "one" and return a random code from here which contains ID 1.

When Input is ID 3, it should check in sheet 'updates', ID 3 is not available in updates sheet, so it should go to sheet 'one' and return a random code which has ID 3.

Basically it should always check the 'updates' sheet first. only if the input ID is not available, it should go to 'One' Sheet.

My data here is dynamic,for example ID 1 values will keep increasing every month, so when a match is found it should return value. Please help on this.

This is the formula I have used, =INDEX(A:A,LARGE(IF(B:B=F1,ROW(B:B)-ROW(B2)+1),INT(RAND()*COUNTIF(B:B,F1)+1)))

My input(ID) is present in the F1 cell. However, this formula always returns the same code A2. However i need some random code each time.

 

[braindiesel]

Book2
	A	B	C	D	E	F	G	H	I	J
1	Code	ID				1	A9		ID	CODE
2	A1	1							1	G1
3	A2	1							1	G2
4	A3	1							1	G3
5	A4	1							1	G4
6	A5	1							3	F1
7	A6	1							3	F2
8	A7	1							3	F3
9	A8	1							3	F4
10	A9	1
11	W1	2
12	L1	2
Sheet1

Cell Formulas
Range	Formula
G1	G1	=IF(COUNTIF(B:B,F1)>0,INDEX(OFFSET(A1,MATCH(F1,B:B,0)-1,0,COUNTIF(B:B,F1),1),RANDBETWEEN(1,COUNTIF(B:B,F1)),1),INDEX(OFFSET(I1,MATCH(F1,I:I,0)-1,1,COUNTIF(I:I,F1),1),RANDBETWEEN(1,COUNTIF(I:I,F1)),1))

 

[Gracy0101]

Book2
	A	B	C	D	E	F	G	H	I	J
1	Code	ID				1	A9		ID	CODE
2	A1	1							1	G1
3	A2	1							1	G2
4	A3	1							1	G3
5	A4	1							1	G4
6	A5	1							3	F1
7	A6	1							3	F2
8	A7	1							3	F3
9	A8	1							3	F4
10	A9	1
11	W1	2
12	L1	2
Sheet1

Cell Formulas
Range	Formula
G1	G1	=IF(COUNTIF(B:B,F1)>0,INDEX(OFFSET(A1,MATCH(F1,B:B,0)-1,0,COUNTIF(B:B,F1),1),RANDBETWEEN(1,COUNTIF(B:B,F1)),1),INDEX(OFFSET(I1,MATCH(F1,I:I,0)-1,1,COUNTIF(I:I,F1),1),RANDBETWEEN(1,COUNTIF(I:I,F1)),1))

Thank you, it worked

 

[braindiesel]

Glad to hear it. Thanks for letting me know