Lookup help

[NewPlayer]

Would appreciate some help in finding a suitable formula.
I have 2 spread sheets. Spreadsheet A is full of data, and Spreadsheet B is
where I wanted the search results to be, say, in cell C1. A1 has a Salesman
name, B1 has an item name.
In sheet A, there are blocks of 10 rows, e.g A1 : M10 and A11: M20 and so
on.
First row of each block has Salesman's name and then Items. e.g. A1 Smith
B1 Hardwood C1 Pine . Under the Items are values from 2nd row to the 10th
row.
The next block are the same with different salesman's name and items ( same
as 1st block.)
Question: How do I do a lookup in Sheet A to look for a Certain Salesman
and then the Item and down to the 3rd or 4th or even 8th row?
In short, can I do a vlookup to look for the salesman and a hlookup from
there to go down the rows from the item?
Thanks for your help.
Much appreciated.
Cking
Would appreciate some help in finding a suitable formula.
I have 2 spread sheets. Spreadsheet A is full of data, and Spreadsheet B is
where I wanted the search results to be, say, in cell C1. A1 has a Salesman
name, B1 has an item name.
In sheet A, there are blocks of 10 rows, e.g A1 : M10 and A11: M20 and so
on.
First row of each block has Salesman's name and then Items. e.g. A1 Smith
B1 Hardwood C1 Pine . Under the Items are values from 2nd row to the 10th
row.
The next block are the same with different salesman's name and items ( same
as 1st block.)
Question: How do I do a lookup in Sheet A to look for a Certain Salesman
and then the Item and down to the 3rd or 4th or even 8th row?
In short, can I do a vlookup to look for the salesman and a hlookup from
there to go down the rows from the item?
Thanks for your help.
Much appreciated.
Cking

 

[JE McGimpsey]

One way:

C1 =INDEX(Sheet1!A:M, MATCH(A2,Sheet1!A:A,FALSE)+rownum,
MATCH(B2,OFFSET(Sheet1!$A$1,MATCH(A2, Sheet1!A:A,FALSE)-1,0,1,13),FALSE))

where rownum = row of data under each header, e.g., using your example,
if A1=Smith and B1=Pine, rownum = 1 will return the value from Sheet1!C2.

where rownum = desired row of data (e.g.,

 

[NewPlayer]

Thanks JE,
Tried the formula, but it didn't go pass the A:M
I tried to put A1:M1000 and still it didn't accept.
By the way, what is the A2 for? In sheets 1 and 2 it is blank.
Also, the -1,0,1,13 what do they represent?

Sorry for so many questions.
Thank you.
Cking

 

[JE McGimpsey]

I inadvertently pasted in the formula from Row 2 (I'd copied it down).
Change the A2s to A1s and B2 to B1 for a formula in C1.

-1 corrects the value returned by ROW() (e.g., if the value is found in
Row 13, ROW() = 13, ROW()-1 =12, which is the row offset you want from
Sheet1!$A$1). The 0 means no column offset (i.e. column A), 1 refers to
the result returning 1 row and thirteen columns.

Net result: If the value in A1 is found in Sheet1, A13, the

OFFSET(Sheet1!$A$1,MATCH(A2, Sheet1!A:A,FALSE)-1,0,1,13)

returns Sheet1!A13:M13

which is the range that should be matched to B1's value (presumably,
"Hardwood", "Pine","Cherry", etc.)

 

[NewPlayer]

Got it !
Thans very much.
Ckig

I inadvertently pasted in the formula from Row 2 (I'd copied it down).
Change the A2s to A1s and B2 to B1 for a formula in C1.

-1 corrects the value returned by ROW() (e.g., if the value is found in
Row 13, ROW() = 13, ROW()-1 =12, which is the row offset you want from
Sheet1!$A$1). The 0 means no column offset (i.e. column A), 1 refers to
the result returning 1 row and thirteen columns.

Net result: If the value in A1 is found in Sheet1, A13, the

OFFSET(Sheet1!$A$1,MATCH(A2, Sheet1!A:A,FALSE)-1,0,1,13)

returns Sheet1!A13:M13

which is the range that should be matched to B1's value (presumably,
"Hardwood", "Pine","Cherry", etc.)