Complicated V Lookup help

[christianbber]

I'm trying to pull data from spreadsheet 2 into spreadsheet 1.

Spreadsheet 1 has names in this order,
John
Sara
Katie


Spreadsheet 2 has data of,
John REG 80
HPI $86.75
FED $16.76
State $24.54

Sara REG 36
HPI $45.75
FED $56.76
State $98.54

Katie REG 14
HPI $67.75
FED $56.76
State $98.54

I know how to use Vlookup but what I dont know is how to specificy which values to pull into spreadsheet one once Vlookup has found the identical value.

More specifically, all I need is the HPI #'s which is in a different column and row .

What I'm trying to get is a command (script?) that says Look at name "Sara", find Sara in the column next to it. Then pull the data thats in column X, row Y.

However, after I'm done with Sara, the next name will have a different column and row # to which I need the data pulled......

Any help is appreciated.

Thank you.

[NBVC]

I'm trying to pull data from spreadsheet 2 into spreadsheet 1.

Spreadsheet 1 has names in this order,
John
Sara
Katie


Spreadsheet 2 has data of,
John REG 80
HPI $86.75
FED $16.76
State $24.54

Sara REG 36
HPI $45.75
FED $56.76
State $98.54

Katie REG 14
HPI $67.75
FED $56.76
State $98.54

I know how to use Vlookup but what I dont know is how to specificy which values to pull into spreadsheet one once Vlookup has found the identical value.

More specifically, all I need is the HPI #'s which is in a different column and row .

What I'm trying to get is a command (script?) that says Look at name "Sara", find Sara in the column next to it. Then pull the data thats in column X, row Y.

However, after I'm done with Sara, the next name will have a different column and row # to which I need the data pulled......

Any help is appreciated.

Thank you.

Maybe better to use INDEX/MATCH

e.g.

=INDEX(Shee2!A:C,MATCH(A2,Sheet2!A:A,0)+1,3)

This looks at table in Sheet2 from columns A:C

It finds matching name in column A, then goes down 1 more row, and to 3rd column (column C).

[martindwilson]

do you mean something like this?

[christianbber]

I attached a picture of what I'm looking at. I simplified it since my original question .

It is a VLOOKUP in a sense but now not only does it have to find the same name "Smith", but it has to find the datat value next to "NBCFFT"

Thank you for your continued help.

[NBVC]

Try:

=INDEX(INDEX($D$1:$D$25,MATCH(A1,$D$1:$D$25,0)):$F$25,MATCH("NBCFFT",INDEX($E$1:$E$25,MATCH(A1,$D$1:$D$25,0)):$E$25,0),3)

adjust ranges to suit.

[martindwilson]

ok like this then