Vlookup/Match - Read Data from 1 table using a value from another

[oblah]

Hi All,

I am stuck.

My wish is to have column G match a value entered on a different worksheet.

Once there is a match I would like column E to be read in alignment to the relevant values in Column G.

From here I want column E to look for an exact match in Column B.

Column B then reads the number of times that value appears & prints that in a cell. (how many times the value from column E appears in column B)

It is also to print the relevant values in Column C
....................................................................
I have attached an example worksheet.

Many thanks

Oblah

[protonLeah]

Range Names:
OrgID_B =Sheet1!$B$2:$B$10
OrgID_E =Sheet1!$E$2:$E$10
UniqueID =Sheet1!$G$2:$G$7

Course Counter:

Please Login or Register  to view this content.

Course Names List:

Please Login or Register  to view this content.

[oblah]

This has worked. Thank you very much.

If you could, could you elaborate slightly on the functionality of ROW in this context. I have never used that functionality before.

Thanks again - this has helped me a lot.

Oblah.

[protonLeah]

Sorry for the delay!

---

The SMALL function returns the k-th smallest value in a data set.
The syntax is: SMALL(array,k)

The formula in A9:A21, is
=IFERROR(INDEX(Sheet1!$A$1:$G$10,SMALL(IF(Sheet1!$B$1:$B$10=INDEX(Sheet1!$E$2:$G$10,MATCH($A$3,Sheet1!$G$2:$G$10,0),1),ROW(Sheet1!$C$1:$C$10)),ROW()-8),3),"")

The array is built with:
=IF(Sheet1!$B$1:$B$10=INDEX(Sheet1!$E$2:$G$10,MATCH($A$3,Sheet1!$G$2:$G$10,0),1),ROW(Sheet1!$C$1:$C$10))

The condition test for the IF function:
Sheet1!$B$1:$B$10=INDEX(Sheet1!$E$2:$G$10,MATCH($A$3,Sheet1!$G$2:$G$10,0),1)

tests the values in the 10 rows of column B against the value returned from Index/match, and builds an array of True and False values.
The value to return if True is evaluated with: ROW(Sheet1!$C$1:$C$10)), Here it returns to array {1,2,3,...,10}

Using the ID number in your test file, 147785, Index returns 2787, which is found in row 5 & 6 of column B..

So, the IF function returns the array {False,False,False,False,5,6,False,False,False,False} to the SMALL function.

The second parameter of the SMALL function, vis., "k", specifies which relative value in the array to return; in this case #1. I used Row(C1) to specify row 1 because I wanted to drag the formula down to row 21. Notice that I could have hard coded "1" in the formula instead. But that would require manually editing each formula with 2,3,...
Also, since the first formula is in column A, row 9, and I wanted the first smallest number, I could have used ROW()-8. However it was easier to use ROW(C1) (Note that any cell in the first row, A1, B1,... would also work).

Finally, SMALL returns 5 and 6 to the INDEX for the first two rows for that particular ID number.

[oblah]

Many thanks for all of this help ProtonLeah!