Creating a Ref # lookup sheet

[kencyr53]

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

 

[Oaktree]

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]

Thnx ill try to

Lead ID: 1234

First Name Last name: address:
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]

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
	A	B	C	D	E	F
1	Lead ID	1234			Lookup ID	4567
2	First Name	Last Name	Address		First Name	Ken
3	Joe	Schmoe	Tulsa		Last Name	cyr
4					Address	53
5	Lead ID	4567
6	First Name	Last Name	Address
7	Ken	cyr	53
Sheet1

 

[kencyr53]

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]

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]

na...no worries, your suggestion worked perfectly...thx again

Ken