Lookup function, help needed

Hi guys

I'm having a pretty simple problem.
I've got 2 excel sheets. The first sheet has 2 columns, column A with numbers 1,2,3,4...,~2000 all the way down, and a column B with specific different numbers all the way down.
In the second excel sheet there are the same numbers appearing as in the column B from sheet one, just spread out randomly.

What I have to do is to search for the numbers in sheet 1, column B, and find their corresponding horizontal number from column A, putting the number from column A in a cell in the second excel sheet.



I've done some research and tried using VLOOKUP and INDEX-MATCH but without success.

Hope you can help

Christopher

Originally Posted by dogberry

Hi

Can you post a sample worksheet so we can see the layout etc dont post confidental information
it will also get you a much better response.


To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.



Chris

Thanks for your quick reply. I try upload 2 example files.

Chris

Originally Posted by :) Sixthsense :)

In Sheet2 - A2 cell

Formula:

Please Login or Register  to view this content.

Drag it down....

Okay. I tried using the function you wrote. In my example excel sheets, if I put =IFERROR(INDEX([1.xlsx]Ark1!$A$2:$A$11;MATCH(A6;[1.xlsx]Ark1!$B$2:$B$11));"") the output is 8. It should be 4.
Have I made any mistakes?

In my other confident document the output number is wrong as well.

Best
Chris

Originally Posted by :) Sixthsense :)

Missed Part
=IFERROR(INDEX([1.xlsx]Ark1!$A$2:$A$11;MATCH(A6;[1.xlsx]Ark1!$B$2:$B$11;0));"")

Thanks! It works

Appreciate it

Chris