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!
Last edited by meggles; 11-19-2013 at 10:54 AM.
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:- MooFormula:
Please Login or Register to view this content.
=address(match(4,n:n,0),match(ag1,indirect(match(4,n:n,0)&":"&match(4,n:n,0)),0))
?
I need a magnifying glass to see your screencaps!
See if this helps...
Data Range
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.
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!
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.
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.
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?
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!
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)
Good deal. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks