concatenate vs and

[Guest]

I have inherited a convoluted worksheet that I need to reference to a complex sheet that I cannot manipulate. The data I need is in the latter sheet. It has several entries for single accounts that I need to pull into the first sheet. i.e.:
Account 4DigitCode Amount1 Amount2
345678 0200 $1000.00 $456.89
345678 7820 $17.89 $17256.01
022789 0200 $10.00 $0.00
022789 1111 $0.00 $500.00

I need to display each amount by 4DigitCode for each account. I thought I could concatenate the account and 4DigitCode in a vlookup, I could have write a formula to lookup the concatenated value and place the two amounts in the new sheet. It didn't work, so I also concatenated the fields in the sheet I want the data to go in (ConcDesired) and tried to vlookup using that field referencing the first vlookup. i.e.

Vlookup1 (Complex Sheet)
Account 4DigitCode Concatenate
345678 0200 3456780200
345678 7820 3456787820
022789 0200 0227890200
022789 1111 0227891111

my formula: VLOOKUP(M7,SLLookup,4,FALSE) returns "#Value"
and VLOOKUP(M7,SLLookup,4,True) returns zeros for everything

where M7 is the cell that had the ConcDesired value and SLLookup is the vlookup table.

Is what I want possible? If yes, how do I make it work?

 

[Frank Kabel]

Hi Bud
your approach should work.You get a #VALUE error (not a #NA error)?.
Some ideas:
- Your SLLOOKUP range starts with the concatenated column
- check if your search criteria and your lookup column BOTH contain
either numbers or text
- The column index in your formula is '4' have you checked that

Frank

 

[millsy]

*I have inherited a convoluted worksheet that I need to reference to
a complex sheet that I cannot manipulate. The data I need is in the
latter sheet. It has several entries for single accounts that I need
to pull into the first sheet. i.e.:
Account 4DigitCode Amount1 Amount2
345678 0200 $1000.00 $456.89
345678 7820 $17.89 $17256.01
022789 0200 $10.00 $0.00
022789 1111 $0.00 $500.00

I need to display each amount by 4DigitCode for each account. I
thought I could concatenate the account and 4DigitCode in a vlookup,
I could have write a formula to lookup the concatenated value and
place the two amounts in the new sheet. It didn't work
, so I also concatenated the fields in the sheet I want the data to
go in (ConcDesired) and tried to vlookup using that field referencing
the first vlookup. i.e.

Vlookup1 (Complex Sheet)
Account 4DigitCode Concatenate
345678 0200 3456780200
345678 7820 3456787820
022789 0200 0227890200
022789 1111 0227891111

my formula: VLOOKUP(M7,SLLookup,4,FALSE) returns "#Value"
and VLOOKUP(M7,SLLookup,4,True) returns zeros for everything

where M7 is the cell that had the ConcDesired value and SLLookup is
the vlookup table.

Is what I want possible? If yes, how do I make it work? *

The #value error is probably because you are mixing string values and
numbers. Looking at your problem, it seems to me that extracting this
data using lookup is going to be pretty difficult anyway. You don't
say how many account codes there are or how many 4 digit codes. In
order to make sure you get all the data you would have to do a separate
lookup for each possible account code / 4 digit code pairing. If it is
possible that new codes could be added to the other spreadsheet you
would have to edit your formulae each time there is a change.

If you use a pivot table to get this data from your uneditable
spreadsheet you won't have to worry about picking up all the
combinations and it won't matter if new codes are added to the other
spreadsheet.

Alternatively, link all the cells in a new worksheet to the uneditable
one and then use Data>Filter>Autofilter to pull up the items that you
want.