Two-way look-up with a twist

[Bad_Handle]

Thanks to this fantastic webiste, I've saved a lot of time by doing two-way lookups with an index and two match fuctions formula

=INDEX($A:$D,MATCH($F3,$A:$A,0),MATCH(G$2,$A$1:$D$1,0))

I was wondering if there is a way to take this one step further.

For the first match funciton, MATCH($F3,$A:$A,0), is there a way to have excel determine that Column A is the column I want to use as the look up array by having it match the column heading in row one?


For example, let's say the heading for column A in MATCH($F3,$A:$A,0), is "Account Number". Is there a way to have it search for "Account Number" in Row 1 and then, after recognizing that "Account number" is in positon 1 of row one, use the entire column for the search array

The formula might look something like this:

MATCH($F3,(Serach for column w/ "Account Number" ,0)



I could find GREAT use for a formula that accomplishes this in a Macro

 

[wigi]

=INDEX($A:$D,MATCH($F3,OFFSET($A:$A,MATCH("Account Number",$A$1:$D$1,0)-1),0),MATCH(G$2,$A$1:$D$1,0))

 

[ravishankar]

Hi
Hi
paste the following codes in the macro window ( Alt F11) and run the macro.

Sub handle()
Dim x As Long, y As Long
Dim z As String
z = Chr(64 + WorksheetFunction.Match("Account number", Range("A1:D1"), 0))
x = Application.WorksheetFunction.Match(Range("F3"), Range(z & ":" & z), 0)
y = Application.WorksheetFunction.Match(Range("G2"), Range("A1:D1"), 0)
Cells(10, 10) = Cells(x, y)
End Sub

It finds column containing account number, finds match for F3 and G2 and returns intersecting value in J10
Ravi

 

[wigi]

Hi
Hi
paste the following codes in the macro window ( Alt F11) and run the macro.

Sub handle()
Dim x As Long, y As Long
Dim z As String
z = Chr(64 + WorksheetFunction.Match("Account number", Range("A1:D1"), 0))
x = Application.WorksheetFunction.Match(Range("F3"), Range(z & ":" & z), 0)
y = Application.WorksheetFunction.Match(Range("G2"), Range("A1:D1"), 0)
Cells(10, 10) = Cells(x, y)
End Sub

It finds column containing account number, finds match for F3 and G2 and returns intersecting value in J10
Ravi

Or:

Sub shortercode()
    Cells(10, 10) = Cells([A1:D1].Find("Account Number").EntireColumn.Find([F3]).Row, [A1:D1].Find([G2]).Column)
End Sub

Wigi

 

[Bad_Handle]

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o>Thank you so much! All of these ideas have opened up doors of new possibilities. With the offset formula, I was able to create a very long formula that accomplished what I needed for a slightly different problem, but the VBA recorder wouldn't accept it (prob a length issue). <o></o>
<o></o>
<o></o>
I'm wondering if either of the macro codes above could be adapted to find the "Account Number" in a table on another worksheet ("Sheet2"), and match the two cells from "Sheet1" on the "Sheet2" table and return the value on "Sheet1". And then apply it to the entire column<o></o>
<o></o>
More simply, <o></o>
Sheet1 contains Column A (Acct #s) and B1 is the heading title "Company Name" <o></o>
Sheet2 contains in unknown columns (among other info) a column for Acct #s and a column for Company Names<o></o>
<o></o>
Select B2. Take the account # in Sheet1 Column A: $A2. Find it in sheet 2 return the intersection of column heading in Sheet1 B$1 and Acct # sheet2. Repeat process for all rows in column B<o></o>
<o></o>
<o></o>
There may not be an easy way to do this. <o></o>
<o></o>
Just want to say thanks one more time. Sorry for the long reply<o></o>
</o>