# Retrieving Cell Data and Reverse Lookup in separate spreadsheet

1. ## Retrieving Cell Data and Reverse Lookup in separate spreadsheet

Hello Everyone,
My name is Melvin and I work as an accountant in a Logistics Company. I am a newbie here and I implore your help in a major predicament with Microsoft Excel.

Let me begin,

Lets say I have 2 spreadsheets.

1. The first document, SALEXCEL_1111 contains a large list of salary values of individuals of different pay grades. The Left Column is PAY GRADES namely with a letter and a number. For example, A1, A2 upto A10, subsequently B1,B2 upto B10. I hope to bring to your attention that these values are not cell references but the actual data within the cells.
And the lower row is that of SENIORITY in years.
_______
A1
A2
..
A10
_______
B1
B2
..
B10 | 2250 | 2500 | 2750 |
_______

SENIORITY | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |

2. The second document, SALEXCEL_2222 is that of a specific company branch office with employee details such as the name field, position and then PAY GRADE.

NAME | POSITION | PAYGRADE | SENIORITY | SALARY

XYZ | Accountant | B10 | 2 | XVALUE

Now the biggest challenge is that, there are different employees with different pay grades and many such branch office spreadsheets. Here thankfully, the main database spreadsheet is a constant.
Is there any way to obtain XVALUE by Looking up the paygrade and the seniority cell values in SALEXCEL_2222 and obtaining the required addresses and then by referencing SALEXCEL_1111 and looking up the cell data value of the PAY GRADE column and once that is obtained in SALEXCEL_1111,
then moving across the SENIORITY ROW to arrive at the correct [XVALUE].

For example,
while in SALEXCEL_2222;
if for the person XYZ who is an accountant with the paygrade B10 and seniority of 2 years,
then the SALARY can be obtained by looking up in SALEXCEL_1111
and looking down at the left PAYGRADE column arriving at B10,
then looking across the SENIORITY column arriving at 2
with the final XVALUE being 2500.

This is the summary of my problematic predicament.
Any help with this will be huuuuuugely and gratefully appreciated with all my heart! :D

2. ## Re: Retrieving Cell Data and Reverse Lookup in separate spreadsheet

Hi,

It's always easier to understand your question if we can see it in context.

Please upload your workbook, or at least a representative sample, with notes referring to the cells involved and manually add the results you expect to see.

Anonymise the data since no doubt it is sensitive.

Regards

3. ## Re: Retrieving Cell Data and Reverse Lookup in separate spreadsheet

Please attach workbook with dummy data similar to your original format ,if possible with example what you expect to do.

4. ## Re: Retrieving Cell Data and Reverse Lookup in separate spreadsheet

Thank you guys for replying. Hope these attachments convey a better picture. ^_^

ATTACHMENTS:
SALEXCEL_1111
http://www.mediafire.com/?ux12tbvpa84yy41

SALEXCEL_2222
http://www.mediafire.com/?2td00b4xtaat5ao

5. ## Re: Retrieving Cell Data and Reverse Lookup in separate spreadsheet

Hi,

Please upload to the forum and not an independent site whose provenance we don't know.

6. ## Re: Retrieving Cell Data and Reverse Lookup in separate spreadsheet

I aplogise for the oversight. I hope this is adequate.

7. ## Re: Retrieving Cell Data and Reverse Lookup in separate spreadsheet

Pl see the attached file.Formulas are entered in E7 cell as array formula and dragged down.
Your feedback and clarifications are welcome.
Note: Both the files should be in same folder.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1