# Creating a Ref # lookup sheet

#### kencyr53

##### New Member
I have a question about creating a ref lookup sheet. We built a sales sheet for 6 individuals and now to QA them, we want to ref the info by call ID #. Prob is that the sheet created, the info like name, address, email, bla bla bla is ref to the cell underneath it.

Example:
first name: Last name:
Joe Schmoe

So when I do a Vlookup or hlookup, we can't get the info to pull. Is there a formula or macro I should be using and if so, could you show me what it is. Been searching for days and dont know what direction I should be heading in

Thanx,
Ken

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Oaktree

##### MrExcel MVP
Welcome to the board.

Can you give a full example of what you'd like to return? Showing a sample of more than one record would help get a feel for your data layout as well...

#### kencyr53

##### New Member
Thnx ill try to

Joe schmoe Tulsa
so if the post doesnt format right lead id =a1 , 1234=b2, first name=b1, last name=b2, address=b3, joe=c1, schmoe=c2,tulsa=c3

now what we want to do is create a sheet by using a lookup of 1234 to give us the data of joe schmoe in tulsa......but the prob is that we cant use v/h lookup because the data for the catagories are entered underneath them rather than to the right of them

#### Oaktree

##### MrExcel MVP
Try something like this:

F1 is the input cell
F2 =INDEX(A:A,MATCH(\$F\$1,B:B,0)+2)
F3 =INDEX(B:B,MATCH(\$F\$1,B:B,0)+2)
F4 =INDEX(C:C,MATCH(\$F\$1,B:B,0)+2)
Book3
ABCDEF
3JoeSchmoeTulsaLast Namecyr
7Kencyr53
Sheet1

#### kencyr53

##### New Member
so if we build a sheet, when we type 1234 if the ref cell, would all the info underneath pull as well? I was asking our IT guy and he seems to think that the id is a seperate record set. He suggested that the info needs to have an associated link to the info via same row to kind of "link" itself to the info. I suggested maybe merging the a-e cells together may work. What do you think? Ill try your suggextion as well.

#### Oaktree

##### MrExcel MVP
Maybe I am misunderstanding your question. I thought you were saying that you had data as in columns A:C in the spreadsheet in my last post and that you were trying to look up data based on an ID (to get columns E:F). It sounds now like you have a database (or two) that you're trying to query against to get the data to look like columns A:C...?

#### kencyr53

##### New Member
na...no worries, your suggestion worked perfectly...thx again

Ken

Replies
4
Views
667
Replies
5
Views
753
Replies
1
Views
767
Replies
0
Views
162
Replies
10
Views
558

1,171,242
Messages
5,874,620
Members
433,060
Latest member
studyiqedu

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back