Share via

Excel spreadsheet, how to cross reference columns

Anonymous
2015-10-22T15:34:19+00:00

I have a spread sheet that has the following columns:

A = numerical lab code

B = CPT/Charge Code

C=Lab name

D = Blank

the next columns were copied from a separate spreadsheet that have the prices for all of our labs:

G = numerical lab code

I = Price

How do I build logic that will compare columns A and G and when they are equal they put the value in Column I into Column D?  Or other wise match up the prices with the lab codes.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2015-10-22T20:23:17+00:00

    Some more clarification.  This may require a macro.

    The formulas listed won't work.  Here's why.

    I've rearranged the columns, here is what I am working with-

    my values start on row 2

    I need A2 to be queried against the ENTIRE column H, and when a match is found, to pull the price from that row which is in Column J and put that price into G2.  

    A2 and G2 represent the row, this logic needs to be applied to row 2-700

    Columns H-J go from 2 to 5447

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2015-10-22T20:08:11+00:00

    The "IFERROR" formula only allows for one comma.

    =IFERROR(value, value_if_error)

    I'm a little confused on how to get that in there and what is the range for?  how many cells down i want it to look?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2015-10-22T20:02:27+00:00

    basically I have two spreadsheets.  Spreadsheet one has a column of lab codes (i'm only mentioning the relevant colums).  Spreadsheet one is going to be my "masterfile" for labs because it has everything I need, except the price.  

    Spreadsheet 2 has a column of lab codes, but it is much larger than spreadsheet one.  It also has the prices.  I'm trying to match up the price and lab code on spreadsheet two to the lab code on spreadsheet one so that spreadsheet one has a column of all the prices with the correct lab code.

    0 comments
  4. HansV 462.6K Reputation points MVP Volunteer Moderator
    2015-10-22T15:41:18+00:00

    Let's say the data begin in row 2.

    In D2, enter the formula

    =IFERROR(VLOOKUP(A2,$G$2:$I$100,3,FALSE),"")

    Adjust the range G2:I100 as needed.

    Fill the formula down from D2 as far as you want.

    Edited to correct typo

    0 comments
  5. Anonymous
    2015-10-22T15:39:46+00:00

    Hi,

    Not sure I fully understand the bit in Bold but maybe the formula below in D2 and drag down.

    compare columns A and G and when they are equal they put the value in Column I into Column D?  Or other wise match up the prices with the lab codes.

    =IF(A2=G2,I2,A2)

    0 comments