Get related data from 2 worksheets

[selvamnarayanan]

Dear Sir
Can you help me
I have 2 worksheets
Sheet 1 10 columns of data, 300 rows
Sheet 2 14 columns of data, 500 rows
Each sheet has common data in 4 columns
Sheet 1 needs to get data from one column (e.g. column 8) in Sheet 2 (exact match data from the 4 common columns )
Sheet 2 needs to get data from one column (e.g. column 10) in Sheet 1 (exact match data from the 4 common columns)
I have tried using VLOOKUP but the answers are inaccurate
Thank you
selvamnarayanan

 

[oldbrewer]

Re: How to get related data from 2 worksheets

if you post an example spreadsheet representing both sheets each 6 columns by 6 rows and show how data is retrieved it will paint a clearer picture...

 

[selvamnarayanan]

Re: How to get related data from 2 worksheets

if you post an example spreadsheet representing both sheets each 6 columns by 6 rows and show how data is retrieved it will paint a clearer picture...

SHEET 1
A B C D E F
Doc Age Race Sex Grade Rank
2020 44 I M A
2022 48 I F A
2028 45 C F C
2029 35 C M C

Need Rank - For common records found in SHEET 2




SHEET 2
A B C D E Grade
Doc Age Race Sex Rank
2020 44 I M 13
2022 48 I F 17
2028 45 C F NA
2029 35 C M NA
2083 49 M F 22
2099 39 M M 15

Need Grade - For common records found in SHEET 1
Sheet 2 could have records not found in Sheet 1 & to be ignored

Thank you sir.
Appreciate your help
selvamnarayanan

 

[oldbrewer]

Re: How to get related data from 2 worksheets

SHEET 1

A

B

C

D

E

F

Doc

Age

Race

Sex

Grade

Rank

2020

44

I

M

A

13

###1

2022

48

I

F

A

17

2028

45

C

F

C

NA

2029

35

C

M

C

NA

Need Rank - For common records found in SHEET 2

SHEET 2

A

B

C

D

E

Doc

Age

Race

Sex

Rank

Grade

2020

44

I

M

13

A

###2

2022

48

I

F

17

A

2028

45

C

F

NA

C

2029

35

C

M

NA

C

2083

49

M

F

22

2099

39

M

M

15

Need Grade - For common records found in SHEET 1

Sheet 2 could have records not found in Sheet 1 & to be ignored

###1

=IF(ISERROR(OFFSET($A$16,MATCH(A4,$A$17:$A$22,0),4)),"",(OFFSET($A$16,MATCH(A4,$A$17:$A$22,0),4)))

###2

=IF(ISERROR(OFFSET($A$3,MATCH(A17,$A$4:$A$7,0),4)),"",(OFFSET($A$3,MATCH(A17,$A$4:$A$7,0),4)))

once they are on 2 sheets you just need to put sheet2! Before the cell references

<colgroup><col span="14"></colgroup><tbody>
</tbody>