VLOOKUP returns results to left of primary key

[bryanhope]

I am working on a large spreadsheet and I am using data from column 7 as the primary key but I need to be able to reference info from columns 1-6 is there anyway to display info from the columns to the left of the primary key ?

Thanks

[BadlySpelledBuoy]

You can use INDEX/MATCH rather than VLOOKUP to do that.

Something like:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Note, the 0 near the end of the formula forces an exact match. There are other options you could use here depending on the situation.

Does that help?

BSB

[bryanhope]

I've never used index and match I am playing with right now to get it to work...

Here is the vlookup formula I am using now =VLOOKUP("*"&A3&"*",'Employee Roster'!A:AZ,1,0)

I am using partial match to return the info I want with the actual data residing on another sheet

[BadlySpelledBuoy]

So with that formula you're just looking up the partial match in column A ( on sheet "Employee Roster") and the formula returns the full match from the same column?

I'm confused as to why you need to look up to the left in that case...?

BSB

[BadlySpelledBuoy]

The formula you gave would be rewritten using INDEX/MATCH like this:

Please Login or Register  to view this content.

BSB

[bryanhope]

Yes that is correct, currently, employee name which is located in range a:az on the employee roster is the primary key I am using the employee name to query, but what I need to do is create a second query where query input is not the name, I want to query by the info located in column 7 but also reference info from columns 1-6

Thank You

[BadlySpelledBuoy]

If you need more help to write the INDEX/MATCH formulas then attach a desensitized version of your workbook and we can go from there.

BSB

[bryanhope]

See photo ive what I am trying to do and maybe you will have an idea. Thank You query.png

[BadlySpelledBuoy]

Which of those cells do you need formulas for and which columns on the Employee Roster sheet pertain to each?
For example "Employment Status is in column A of Employee Roster sheet"

BSB

[bryanhope]

Appreciate your help. all data is on the Employee roster sheet. See below how I have set up

Employment Status : =VLOOKUP("*"&A3&"*",'Employee Roster'!A:AZ,4,0)
Locations: =VLOOKUP("*"&A3&"*",'Employee Roster'!A:AZ,9,0)
Classification: =VLOOKUP("*"&A3&"*",'Employee Roster'!A:AZ,5,0)
Cubicle: =VLOOKUP("*"&A3&"*",'Employee Roster'!A:AZ,7,0)

I am trying to create an additional query where I can query someone by the cubcile location, the cubicle is in column 7, and I want to reference the info the columns 1-6 as well as columns 8-15 I am trying to create a table nearly the same as you see but the query will be by location instead of name. If i could figure out how to query by cubicle location and reference employment status I can figure out the rest of the fields.

[BadlySpelledBuoy]

So with Employment Status in column D and Cubicle in column G, the below should work. You will need to amend A3 to whichever cell the cubicle number will be entered into to perform the search (if it's not A3 of course!):

Please Login or Register  to view this content.

To look up other details, simply change D:D to whichever column you wish to return a value from. The rest of the formula stays the same.

BSB

[bryanhope]

That worked ! Thank You !

[BadlySpelledBuoy]

No probs. Happy to help

BSB