Multple Criteria "Vlookup"

[NotSwank]

Essentially I want to do a lookup matching multiple criteria that is referencing a multitude of other formulas.

The Formula I have now is doing something right as when I have a match I get #N/A and when I don't I get #Value

I just need it to return the correct cell. Here is what I have:

Please Login or Register  to view this content.

Note that the first value I'm trying to match is a particular.

I want to say if column B contains the T4 value and in the same row column F matches exactly O4 then return the 5th columns value.

I hope that all makes sense.

Thanks for any advice that you can offer!

[Ace_XL]

Untested

Please Login or Register  to view this content.

[NotSwank]

That is working to reference everything, but the returning value isn't coming back correct it's coming back as 0. It should return column 5 of the row where those two arguments are valid.

[NotSwank]

I am getting some info finally with the formula, but they are not matching what they should. I think it may have to do with the "contains" first lookup. Does anyone have any suggestions?

Please Login or Register  to view this content.

[Tony Valko]

Can you post a SMALL sample file and show us what result you expect?

A SMALL sample file will have about 20 rows and about 4-5 columns of data.

[NotSwank]

I think I may know a portion of it is that the first lookup is not unique.

A little history on this file. I've removed some tabs that arent important to the issue. The formula I am trying to create is in a living document, when the document is "updated" the formulas will update and find the relevant information. It needs to find the correct processor number and the matching dollar values.

Here is a sample file.

The formula column is highlighted in yellow.

http://www.sendspace.com/file/z85d29

[FDibbins]

Please upload your workbook to the forum, not all members are able - or willing - to download files from file-hosting sites

[NotSwank]

How do I go about doing that?

[ConneXionLost]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Tony Valko]

Here is a sample file.

http://www.sendspace.com/file/z85d29

Sorry, that file is too big for me (165kb) plus it's got VBA code.

On a side note...

[rant]

99% of free file hosting sites suck!

There were 4 "Download" buttons on the page. Which one was to download the file? Those A-holes intentionally make it confusing so that you'll click the wrong "Download" button and get blasted with more ads (or worse!). Effing A-holes!

[/rant]

[FDibbins]

+1 to that, Tony

[NotSwank]

Bank Recon Sample File.xlsx

Sorry about that folks! I appreciate the help very much!

[NotSwank]

I removed the VBA also, FYI.

[Tony Valko]

OK, I downloaded your file.

Can you explain what you want to do based on this sample file? Be very specific!

The formula in column U of the Deposits sheet refers to a file named Bank Recon2.xlsm. I assume that was your original posted sample file?

[NotSwank]

Yeah that was the original.

Lets use Deposit Tab - Row 17 as an example. I want a formula in U17 to lookup T17 to find in Column B of Tab "Statement Copy" the row that contains T17 (there are extra digits in column B) and also Lookup O14 in Column F of tab "Statement Copy", having the Number returned in column G where the row matches both criteria.

In T17 example I'd expect it to return Cell G5 of the Statement Copy Tab or "9488893532"

Thanks for your help Tony!

[NotSwank]

Ideally there should only be one combination of both the numbers.

[Tony Valko]

Yeah that was the original.

Lets use Deposit Tab - Row 17 as an example. I want a formula in U17 to lookup T17 to find in Column B of Tab "Statement Copy" the row that contains T17 (there are extra digits in column B)

OK, I understand that much...

and also Lookup O14 in Column F of tab "Statement Copy", having the Number returned in column G where the row matches both criteria.

But I'm totally confused with that!

Did you mean lookup O17?

Is this what you want to do...

Lookup O17 in column F of the Statement Copy sheet and lookup T17 in column B of the Statement Copy sheet and return the value from the corresponding cell in column G of the Statement Copy sheet

[NotSwank]

Lookup O17 in column F of the Statement Copy sheet and lookup T17 in column B of the Statement Copy sheet and return the value from the corresponding cell in column G of the Statement Copy sheet

That is exactly this. I meant O17.

[Tony Valko]

Try this array formula** entered in U4 on the Deposits sheet:

=IFERROR(INDEX('Statement Copy'!G$3:G$15,MATCH(O4,IF(ISNUMBER(FIND(T4,'Statement Copy'!B$3:B$15)),'Statement Copy'!F$3:F$15),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down as needed.

[NotSwank]

Is there anyway to automate CTRL SHIFT ENTER? or do a whole column at once?

[NotSwank]

BTW the worked great! I'm using JEtReports so it duplicates the rows automatically. I think I will have to set a macro to duplicate the CTRL,SHIFT,ENTER.

[ChemistB]

Once you do CNTRL SHFT ENTER, you can copy and paste and the contrl shft enter (array) attribute pastes right along with it.

[Tony Valko]

Good deal. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.