vertical / horizontal lookup

P

proctk

Well-known Member
Joined
Dec 24, 2004
Messages
840
Hi I want to look up accross cells then when a match is found look down that column.

I tried this =SUMPRODUCT(--('00042'!A7:F7="June"),SUMPRODUCT('00042'!H1:H1000=Formulas!A5),('00042'!E1:E1000))

no luck as sumproducts want the look up range to be the same size. Column E needs to be a variable as not all sheets have the exact same format and I want to pull the value from the column with the header "June". The header will always be on row 7.


Any help is great!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
proctk said:
Hi I want to look up accross cells then when a match is found look down that column.

I tried this =SUMPRODUCT(--('00042'!A7:F7="June"),SUMPRODUCT('00042'!H1:H1000=Formulas!A5),('00042'!E1:E1000))

no luck as sumproducts want the look up range to be the same size. Column E needs to be a variable as not all sheets have the exact same format and I want to pull the value from the column with the header "June". The header will always be on row 7.


Any help is great!
Click to expand...
It looks like you're trying to find the column labeled June but what are you doing with column E?
 
Upvote 0
You're right, the column that i'm looking for is labled June.

I'll try to explain better

Step 1 - Look for value "Loans" accross row 7 when it is found then find "Variable 1"

Step 2 - Look for value "June" accross Row 7 when it is found then return the value than is next to variable 1 under the row labeled June

Hope this heps! thank you.
 
Upvote 0
here is something I found by searching google

=INDEX('00042'!A1:N1226,MATCH("June",'00042'!A7:J7,FALSE),MATCH(Formulas!A5,'00042'!E6:I1219,FALSE))

does not work yet..... maybe, i'm on the right track!
 
Upvote 0
There was only 5 sheets that don't match the formating and it's an easy fix so I will just do the edit each time.


Here is my next challenge. The data is being pulled in to the spreadsheet via web query

everything works fine with this formula to lookup the data but when there is a negative it returns 0

formula

=SUMPRODUCT(--(INDIRECT("'"&Formulas!D5&"'!$G$1:$G$1000")=Formulas!$A$5),(INDIRECT("'"&Formulas!D5&"'!$D$1:$D$1000")))


Sample data as it looks when it's imported


1,028-
74-
1

103-

176-

Positive numbers are right justed on import and negative numbers are right justified.

Thank you.
 
Upvote 0
proctk said:
You're right, the column that i'm looking for is labled June.

I'll try to explain better

Step 1 - Look for value "Loans" accross row 7 when it is found then find "Variable 1"

Step 2 - Look for value "June" accross Row 7 when it is found then return the value than is next to variable 1 under the row labeled June

Hope this heps! thank you.
Click to expand...
Well, not a whole lot.

You say to look across row 7 for BOTH "loans" and "June"?
 
Upvote 0
proctk said:
here is something I found by searching google

=INDEX('00042'!A1:N1226,MATCH("June",'00042'!A7:J7,FALSE),MATCH(Formulas!A5,'00042'!E6:I1219,FALSE))

does not work yet..... maybe, i'm on the right track!
Click to expand...

Care to post a small sample along with the desired result?
 
Upvote 0
proctk said:
There was only 5 sheets that don't match the formating and it's an easy fix so I will just do the edit each time.


Here is my next challenge. The data is being pulled in to the spreadsheet via web query

everything works fine with this formula to lookup the data but when there is a negative it returns 0

formula

=SUMPRODUCT(--(INDIRECT("'"&Formulas!D5&"'!$G$1:$G$1000")=Formulas!$A$5),(INDIRECT("'"&Formulas!D5&"'!$D$1:$D$1000")))


Sample data as it looks when it's imported


1,028-
74-
1

103-

176-

Positive numbers are right justed on import and negative numbers are right justified.

Thank you.
Click to expand...
That data is not being evaluated as negative numbers.

What version of Excel are you using?

Select the range of numbers
Data>Text to Columns
Next>Next
Click the Advanced button
Check: Trailing minus for negative numbers
OK
Finish
 
Upvote 0
This worked but there are 25 sheets in the spreadsheet and each sheet has 20 columns that have to be edited. I tried selecting the entire sheeet but it will not let me change it this way. It want me to do one row at a time. Is there a faster way?

Thank you
 
Upvote 0
Here is a macro I just created using the macro recored... Looks ugly to me and I'm not an expert on this by any means

is there anyway to clean this up?

Code: 
Sub Edit_Text()
'
' Edit_Text Macro

'

'
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 12
    ActiveWindow.ScrollRow = 1
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("5066").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("5686").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("20172").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("20552").Select
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("42").Select
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("15396").Select
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("34702").Select
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Range("H28:K30").Select
    Range("K30").Activate
    Sheets("42192").Select
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("46672").Select
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("54882").Select
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("55376").Select
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("21972").Select
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=2
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheets("30932").Select
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("41012").Select
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("41962").Select
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("57836").Select
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("65706").Select
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("67132").Select
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("70342").Select
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("73742").Select
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("80572").Select
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("85696").Select
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Sheets("93062").Select
    Columns("D:D").Select
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Sheet2").Select
    Range("B4").Select
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Macro/VBA for lookups
Replies
9
Views
161
mumps
mumps
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
612
Peter_SSs
Peter_SSs
N
  • Solved
Lookup horizontally, that will populate a vertical list based on a cell having any value
Replies
9
Views
501
Fluff
Fluff
C
  • Question Question
Excel formula to dynamically reference a range using values from a horizontally and vertically spilled ranges
Replies
1
Views
1K
cadams1979
C
D
  • Solved
INDEX-MATCH with a vertical lookup
Replies
4
Views
583
Darren_workforce
D

Forum statistics

Threads
1,224,609
Messages
6,179,874
Members
452,949
Latest member
Dupuhini

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top