Multiple Vlookups

[madadd]

Hello all,

I'm trying to lookup 2 different values in another workbook and return a value based on the conditions in other columns.

For example:

In sheet1:
A1 = abc
B1 = 01

In sheet2:

somewhere in column A is 3 or 4 occurrences of ABC
somewhere in column B within those 3 or 4 occurrences are 1 or 2 occurrences of 01 next to it.
In the 1 or 2 occurrences where A and B match up on the same row (lets say this happens in row 995 for example's sake), I now need to look at column C row 995, if there's a number >0, use the number in column D, else use the number in column E.

Hopefully that makes sense. Thanks.

[martindwilson]

nope not at all, attach a workbook showing example/s of what you want/don't want

[TMS]

Something like:

=INDEX(Sheet2!$C$1:$C$100,MATCH(A1&B1,Sheet2!$A$1:$A$100&Sheet2!$B$1:$B$100,0))

Array entered with Ctrl-Shift- Enter rather than just Enter.

Regards

[madadd]

I've attached an example. Sheet2 represents the other workbook.

[TMS]

Example 1:

=IF(ISERROR(MATCH(A2&B2,Sheet2!$A$1:$A$100&Sheet2!$B$1:$B$100,0)),"Not Found",IF(INDEX(Sheet2!$C$1:$C$100,MATCH(A2&B2,Sheet2!$A$1:$A$100&Sheet2!$B$1:$B$100,0))>0,INDEX(Sheet2!$D$1:$D$100,MATCH(A2&B2,Sheet2!$A$1:$A$100&Sheet2!$B$1:$B$100,0)),INDEX(Sheet2!$E$1:$E$100,MATCH(A2&B2,Sheet2!$A$1:$A$100&Sheet2!$B$1:$B$100,0))))

Array Entered.

I'll leave you to translate that for example 2 ;-)

Regards

[TMS]

Example 2, to save you some time:

=IF(ISERROR(MATCH(A7&B7,Sheet2!$A$1:$A$100&Sheet2!$I$1:$I$100,0)),"Not Found",IF(INDEX(Sheet2!$J$1:$J$100,MATCH(A7&B7,Sheet2!$A$1:$A$100&Sheet2!$I$1:$I$100,0))>0,INDEX(Sheet2!$K$1:$K$100,MATCH(A7&B7,Sheet2!$A$1:$A$100&Sheet2!$I$1:$I$100,0)),INDEX(Sheet2!$L$1:$L$100,MATCH(A7&B7,Sheet2!$A$1:$A$100&Sheet2!$I$1:$I$100,0))))

Array Entered

Regards

[madadd]

Excellent! Thank you TMShucks. It took a little to work out the conversion to my actual sheet and snuff out the syntax errors, but that's done and it works great. One very small complaint, I don't suppose it's possible to use entire column references? I tried and it error-ed out on me. I tried something simple even like:

Please Login or Register  to view this content.

and no go. I assume it has something to do with array references, but whatever. Easy workaround.

[TMS]

You're welcome. Thanks for the rep.

You can probably do whole column references in 2007+ but it wouldn't be recommended.

If you think about it, you're multiplying two columns of 64000+ entries so it will be slow and memory hungry ... even more so with over a million rows in 2007.

Regards

[madadd]

I don't know if this is abiding by forum rules (I dind't find anything in there) but I have a follow up question.

So based on your eloquent formula, I've modified it to the following:

Please Login or Register  to view this content.

What I need to do is further modify the section:

Please Login or Register  to view this content.

I realized I can't point at just this column (the S column). I need to choose between 13 different columns based on a value I have in the current worksheet, column H. Column H (located in the worksheet where the formula is, not the 090911 FGI.xls) is going to contain the correct column title for which to search:

Please Login or Register  to view this content.

So, I thought about using an Hlookup, and I came up with

Please Login or Register  to view this content.

This mostly gives me an #N/A error, but sometimes it works...very weird can't figure out the pattern of why or why not. So maybe some kind of integration between this and the original?

[TMS]

I don't know if this is abiding by forum rules (I dind't find anything in there) but I have a follow up question.

Strictly, no ... and you would be better starting a new thread and providing a cross reference back. That way, you're likely to get more input from a wider group of people.

Regards

[madadd]

Oy, thnx and done.