Look Up I\'m trying to figure out how to use a formula that looks up data on one

Question

Look Up

I'm trying to figure out how to use a formula that looks up data on one sheet to display data on another. For example I would input the transaction number on one sheet and it would use that to pull the PO number on another. My first sheet (well call this sheet 1) which has the basic information, and is more of a tracker has the following column headers

My Other sheet has the following Headers (well call this one sheet 2)

What I want to do is be able to input the transaction number into sheet 2 and have it pull respectively all the rest of the information into the rescpective cells from sheet 1. Thee trick is, that there can bee several lines of transaction numbers so it would need to look it up as containing a string of numbers not matching. I know for using the lookup formula I would need to have the data on sheet 1 sorted in ascending or descending order, but I need the function to work not dependent on how sheet 1 is sorted.

Is this possible, and if so how???

Thank youu

Explanation / Answer

With the LOOKUP() formula you often have to have your information sorted, but with the VLOOKUP() function you do not! The only requirement is that the data you are trying to match in your lookup table (on that first sheet) is unique, because all of the lookup-type formulas (LOOKUP(), VLOOKUP() and HLOOKUP() all stop when they hit the first match).

We need to know which fields/columns from sheet 1 relate to which columns in sheet 2. Is the Transaction Number on Sheet2 the same as Tracking Number on Sheet1?

Whatever field you are trying to match on sheet1 needs to be the leftmost column of a group of columns containing all of the different items you want to retrieve. It does not have to be the leftmost column of all the data - just of the data you want to be returned.

For example's sake, lets say these fields correspond between the 2 sheets:

   Sheet 1                        Sheet2

Tracking Number           Transaction Number

PO                                  PO Number

Description                    Description

Contract Specialist         Contract Specialist

Amount                          Amount

-----                                Investigator (no matching field on sheet 1?)

If you are going to match based on the Transaction Number and finding those on Sheet 1, then the Tracking Number column on Sheet1 must come before any of the other fields to be retrieved. It could be like this

Uploaded to Sharepoint

Contract Type

PI

Docs

Tracking Number

PO

Description

Contract Specialist

Amount

Security Checklist

If "Uploaded to SharePoint" is in column A, that puts Tracking Number in column E.

Over on Sheet2, assume the Transaction Number is in column A, and we're starting at row 2; then in B2 you might have a formula that looks like this:

=VLOOKUP(A2,Sheet1!$E:$J,2,False)

This would return the PO entry from Sheet1 for a Tracking Number on that sheet matching the Transaction Number on Sheet2.

The ,2, part of the formula says when a maatch is found, return the value from the 2nd column of the lookup table. Use ,3, to get the Description, ,4, to get the Contract Specialist, ,5, forr the Amount - and we can ignore the Security Checklist even though I included it as the table just for this example. IF 'Investigator' was there instead, the ,6, would return that.

The ,False) part says find an exact match aand the list of entries in the first column of the table may not be (does not have to be) in any particular order.

Does that help any???

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.