+ Reply to Thread
Results 1 to 11 of 11

Excel Cross-Reference Formula?

  1. #1
    Registered User
    Join Date
    01-22-2008
    Posts
    50

    Excel Cross-Reference Formula?

    I have a table - I would like excel to look down a particular column and across a particular row and find the cross reference...

    For example,

    If the value in column N = 4, select from that row
    Then look at value in cell AG1. Find that along row 4 and return cell that meet both criteria.

    There has to be a way to do this in excel... can anyone assist?

    Thanks in advance!
    Attached Images Attached Images
    Last edited by meggles; 11-19-2013 at 10:54 AM.

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,842

    Re: Excel Cross-Reference Formula?

    In the future, please don't post pictures! Post dummy data or something - it makes our jobs easier.

    But you can give this a try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    - Moo

  3. #3
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Excel Cross-Reference Formula?

    =address(match(4,n:n,0),match(ag1,indirect(match(4,n:n,0)&":"&match(4,n:n,0)),0))
    ?

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Excel Cross-Reference Formula?

    I need a magnifying glass to see your screencaps!

    See if this helps...


    Data Range
    A
    B
    C
    D
    E
    1
    BinA
    BinB
    BinC
    BinD
    2
    Item1
    2
    69
    75
    86
    3
    Item2
    43
    81
    66
    58
    4
    Item3
    55
    11
    74
    13
    5
    Item4
    28
    22
    33
    57
    6
    7
    Item3
    BinC
    74


    This formula entered in C7:

    =VLOOKUP(A7,A1:E5,MATCH(B7,A1:E1,0),0)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    01-22-2008
    Posts
    50

    Re: Excel Cross-Reference Formula?

    Thanks everyone - sorry about images. I've tried all three formulas, and they either return #N/A or #REF!. I've made a dummy sheet and attached it. Any further advice? Thanks!
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Excel Cross-Reference Formula?

    There is no value of 4 in column N, I'm guessing you meant "if the value in column A is four" and that you want to look up the value in a grid. I misunderstood your original post.
    Try:
    =VLOOKUP(4,A1:W59,MATCH(0.2,5:5,1),FALSE)
    based on Tony's suggestion.

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Excel Cross-Reference Formula?

    One (or maybe both) of the lookup criteria are not exactly as they display.

    Manually re-enter 0.2 in both cells S1 and F5 and the VLOOKUP formula works.

  8. #8
    Registered User
    Join Date
    01-22-2008
    Posts
    50

    Re: Excel Cross-Reference Formula?

    Thanks - works well in dummy data! Now have a new issue, in that I'm referencing two sheets....

    The actual formula looks like:

    =VLOOKUP(4,'Die Type Rec Metric Sheet'!O1:JF58,MATCH(B14,'Die Type Rec Metric Sheet'!4:'Die Type Rec Metric Sheet'!4,1),FALSE)

    I think I have the 'Die... for the Match lookup in the wrong place and it doesn't know which row to look at. Any suggestions?

  9. #9
    Registered User
    Join Date
    01-22-2008
    Posts
    50

    Re: Excel Cross-Reference Formula?

    Figured it out...

    VLOOKUP(4,'Die Type Rec Metric Sheet'!O1:JF58,MATCH(B14,'Die Type Rec Metric Sheet'!4:4,1),FALSE)

    THANKS Again!

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Excel Cross-Reference Formula?

    It looks like you're using the entire row 4 for the column match.

    Narrow down that range to be relative to the range address of the table_array. Maybe this:

    =VLOOKUP(4,'Die Type Rec Metric Sheet'!O1:JF58,MATCH(B14,'Die Type Rec Metric Sheet'!O4:JF4,1),0)

  11. #11
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Excel Cross-Reference Formula?

    Good deal. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Cross-reference formula help please
    By elite-fusion in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-13-2013, 11:58 PM
  2. Formula and Comparison/Cross Reference Help!
    By Matthew.Newlove in forum Excel General
    Replies: 2
    Last Post: 04-23-2012, 02:49 AM
  3. Replies: 2
    Last Post: 05-21-2009, 01:54 PM
  4. formula to cross reference
    By excellentexcel in forum Excel General
    Replies: 3
    Last Post: 03-04-2009, 04:22 PM
  5. Cross Reference of Excel Workbook
    By LinzNac in forum Excel General
    Replies: 1
    Last Post: 02-09-2005, 09:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1