# Excel Cross-Reference Formula?

1. ## 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?

2. ## 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:
`Please Login or Register  to view this content.`
- Moo

?

4. ## 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)

5. ## 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!

6. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Excel Cross-Reference Formula?

Good deal. Thanks for the feedback!

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

#### 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