VLOOKUP, INDEX & MATCH Not working

[Bob]

Not sure if I'm constructing the function wrong or not but
several attempts have not succeeded. I've got one
spreadsheet with the following table:

Project# Work Request Cap/Exp.
11111 COD Expense
40090 COD Capital
40090 PJM Expense
etc.....

My second spreadsheet has the same column headers except
the data in the cap/exp. column is blank and I'd like to
populate it by using a function. I've tried Vlookup, index
& match and I'm getting error messages for both. Does
anyone have any other sugguestions?

Thanks in advance.

Bob

 

[Dave R.]

Bob, kindly keep same discussions in one thread. It's easy for people to
keep track of it that way to make sure the solutions are working. Also,
people who replied to the first several threads may feel as if their input
wasn't useful to the poster, so he's looking elsewhere, in which case those
people may not be interested in the newly stated issues of the problem.. and
it takes you much longer to get an answer.

I think people who offer a solution will be more than happy to tweak it for
you until it works, unless you abandon their attempts by starting a new
thread.

 

[Peo Sjoblom]

One way

=INDEX('C:\Documents and Settings\PSjoblom\Desktop\[test
me.xls]Sheet1'!$C$1:$C$20,MATCH(1,('C:\Documents and
Settings\PSjoblom\Desktop\[test me.xls]Sheet1'!$A$1:$A$20='C:\Documents and
Settings\PSjoblom\Desktop\[test me.xls]Sheet1'!A2)*('C:\Documents and
Settings\PSjoblom\Desktop\[test me.xls]Sheet1'!$B$1:$B$20='C:\Documents and
Settings\PSjoblom\Desktop\[test me.xls]Sheet1'!B2),0))

that how it would look if the other workbook is closed, copy down after
using your own path and names
Fortunately if you have both workbooks open when you apply the formula you
can use

=INDEX('[test me.xls]Sheet1'!$C$1:$C$20,MATCH(1,('[test
me.xls]Sheet1'!$A$1:$A$20='[test me.xls]Sheet1'!A2)*('[test
me.xls]Sheet1'!$B$1:$B$20='[test me.xls]Sheet1'!B2),0))

Once again adapt to fit your data (names, ranges etc)

the formula has to be entered with ctrl + shift & enter
when you copy it down it will return what's in the 3rd column (Cap/Exp)

 

[Harlan Grove]

Not sure if I'm constructing the function wrong or not but
several attempts have not succeeded. I've got one
spreadsheet with the following table:

Project# Work Request Cap/Exp.
11111 COD Expense
40090 COD Capital
40090 PJM Expense
etc.....

My second spreadsheet has the same column headers except
the data in the cap/exp. column is blank and I'd like to
populate it by using a function. I've tried Vlookup, index
& match and I'm getting error messages for both. Does
anyone have any other sugguestions?

Do you mean you're trying to look up Cap/Exp values from the original table
based on the two columns Project# and Work Request? If so, you need INDEX/MATCH
rather than VLOOKUP. If your original table were named Tbl, Project#, Work
Request and Cap/Exp fields were, respectively, the first, second and third
columns in Tbl, and the current values in the second worksheet for Project# and
Work Request fields were A2 and B2, respectively, try the array formula

=INDEX(Tbl,MATCH(1,COUNTIF(A2,INDEX(Tbl,0,1))*COUNTIF(B2,INDEX(Tbl,0,2)),0),3)

 

[Dave R.]

If this can be done using '&', why use the COUNTIF and additional INDEX
functions?

{=INDEX(Sheet1!I$6:I$8,MATCH(B2&C2,Sheet1!G$6:G$8&Sheet1!H$6:H$8,0))}

 

[Frank Kabel]

Hi Bob
have you tried my suggestion and if yes what exact error did the
formula return.
I also agree with Dave to stay in the same thread

 

[Harlan Grove]

If this can be done using '&', why use the COUNTIF and additional INDEX
functions?

{=INDEX(Sheet1!I$6:I$8,MATCH(B2&C2,Sheet1!G$6:G$8&Sheet1!H$6:H$8,0))}

...

Why? Experience.

While not the case in the OP's sample data, it's more often than not a bad idea
to assume you never need field separators. If the OP could have the following
Product# and Work Request fields

Product# Work Request
123456 7AB
1234567 AB

then simple concatenation won't work. Again, this isn't the case with the OP's
sample data, but this happens often enough that it's wise to protect against it.

As for the multiple INDEX calls, they avoid the need to hardcode addresses. The
OP's sample data ended with the line 'etc.....', which I take to mean that the
actual original table isn't a mere 4 rows including field names in the top row.
Using INDEX allows me to refer to the original table with a single defined name
Tbl. Granted I'm hardcoding the field positions within Tbl, but that's a level
of expedience I can live with (field positions usually don't vary, but the
number of rows in a table can vary frequently as records are added and deleted).

 

[Harlan Grove]

...
...

=INDEX('[test me.xls]Sheet1'!$C$1:$C$20,
MATCH(1,('[test me.xls]Sheet1'!$A$1:$A$20='[test me.xls]Sheet1'!A2)
*('[test me.xls]Sheet1'!$B$1:$B$20='[test me.xls]Sheet1'!B2),0))

...

More sensible than my approach, though the way I read the OP it's likely the
single cell range references aren't in the same file/worksheet as the table.

 

[Bob]

Frank,
Thanks for your assistance. I tried your sugguestion and
recieved #VALUE! as an error message.

Bob

 

[Frank Kabel]

Hi
try Peo's or Harlan's suggestion. Do they work for you?

 

[Bob]

Hi,
I tried Harlan's sugguestion and got #N/A so maybe I'm
typing something incorrectly.

Thanks.

 

[Frank Kabel]

Hi
the #NA values indicates that there's no match. As you can see from the
slightly differing solutions Harlan, Peo and I provided to you we (or
at least I'm no that sure) are not sure how your spreadsheet is exactly
designed. So you may please provide the following detail:
1. Are we talking about differetn workbooks or just worksheets
2. NAME the worksheets/workbooks
3. Give some example data (in plain text - no attachment please) for
both sheets:
-> the sheet with the lookup range
-> the sheet where the found value should be inserted
-> Define based on these example data your expected result

Normally all of the provided solutions should work. So probably there's
either no match or you have to adapt the ranges (which we all could
only guess in our solutions) to your specific requirements

 

[Guest]

Hi
I've got one workbook with 2 worksheets: The table I have has three columns of data can be called Sheet1. I have the table constructed like this

Column J Column K Column
Project # Activity Code Cap/Ex
11111 ANL Expens
11111 COD Expens
40090 COD Capital
Etc...

The range of this data will be from J2 thru about L50

The other worksheet is called data. It is constructed like this
Column O Column P Column
Project # Activity Code Cap/Ex
11111 COD
40090 CO
11111 HD
Etc........

This worksheet will be from O2 thru about R15000. So basically I'm looking to populate Column R thru a function based on the table in sheet1. For example, Column R values in this example should be Exp, Cap and Exp. I probably could move the table on sheet1 to the data info if it would make things easier

Thanks

Bo

 

[Frank Kabel]

Hi Bob
enter the following in R2
as array formula (entered with CTRL+SHIFT+ENTER)
=INDEX('sheet1'!$L$1:$L100,MATCH(1,('sheet1'!$J$1:$J$100=O2)*('sheet1'!
$K$1:$K$100=P2),0))

and copy this down. If this still returns an #NA error check if you
have spaces or other characters in your columns

 

[Guest]

Thanks Frank
I will try this tomorrow morning as the spreadsheet is on my work computer. So I use L1, J1, and K1 in the formula even though the actual data starts in L2, J2, and K2? L1, J1, and K1 are my column headings.

Maybe I'll try a short example now and see if it works out

Thanks

Bo

 

[Frank Kabel]

Hi Bob
no problem with the column headers included

 

[Guest]

Hi Frank
I think I finally got it. Tried it with a short 10 line example and it seems to be working. Thanks for all your help


Bo