Selective Lookup Help

[Allanafair]

Hi,
I've been working with this workbook for days now and need some help from the pros. I'm trying to use a lookup function for a set of data that looks like the attached:


I need it to search the sheet by the login id (hilighted in example), and return the 2nd and 6th # values (hilighted in example) in the row that starts with Grand, which is the first occurrance of grand in Column A after that specific login ID occurs in column D.

This is making me crazy, i'm not sure if i can do this with a formula, a table, or a macro. Any excel geniuses out there that can help me?

[JohnTopley]

There are instructions at the top of the page explaining how to attach your sample workbook.

[Allanafair]

Thank you! Brand New

[JohnTopley]

I assume (dangerous!) that there are multiple entries of LOGON IDs on a sheet with a variable number of entries (rows) per ID???

[Allanafair]

That is a correct assumption. Sometimes the same login ID can be listed twice (page split) but there is always only one GRAND TOTAL per Login ID entry. The number of rows can vary entirely from 1 to 50+ because it's based on the amount of orders a person fills in that day. Does that help?

[JohnTopley]

Try

in D2

=VLOOKUP("GRAND",OFFSET(Sheet1!$A$1,MATCH($A2,Sheet1!$D:$D,0),,1000,14),4,FALSE)

in E2

=VLOOKUP("GRAND",OFFSET(Sheet1!$A$1,MATCH($A2,Sheet1!$D:$D,0),,1000,14),9,FALSE)


Please try with 2 of same Logon ID

[Allanafair]

THAT WORKED!!! Thank you so much!

[JohnTopley]

You're welcome.

NOTE: If you have more than 1000 rows, then change the 1000 in the formulae