Multiple VLookup Values

Hello All,

I am wondering if someone can help me with a custom Macro using the
VLOOKUP logic.

I need to do something like:

VLOOKUP(lookup_value1,lookup_value2,table_array,col_index_num,range_lookup)

Where lookup_value1 AND lookup_value2 must be there for the item in
col_index to be returned.

So I guess I would need to let the macro which column lookup_value1 and
lookup_value2 should test in?

Does this make sense?

Alex

0
2/11/2006 3:19:34 AM
excel 39879 articles. 2 followers. Follow

11 Replies
363 Views

Similar Articles

[PageSpeed] 38

Yep.  But you don't even need a macro to do this...

You can use this kind of syntax:

=index(othersheet!$c$1:$c$100,
   match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
(one cell)

This is an array formula.  Hit ctrl-shift-enter instead of enter.  If you do it
correctly, excel will wrap curly brackets {} around your formula.  (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
   match(1,(a2=othersheet!$a$1:$a$100)
          *(b2=othersheet!$b$1:$b$100)
          *(c2=othersheet!$c$1:$c$100),0))

(still an array formula)

alexfflores@gmail.com wrote:
> 
> Hello All,
> 
> I am wondering if someone can help me with a custom Macro using the
> VLOOKUP logic.
> 
> I need to do something like:
> 
> VLOOKUP(lookup_value1,lookup_value2,table_array,col_index_num,range_lookup)
> 
> Where lookup_value1 AND lookup_value2 must be there for the item in
> col_index to be returned.
> 
> So I guess I would need to let the macro which column lookup_value1 and
> lookup_value2 should test in?
> 
> Does this make sense?
> 
> Alex

-- 

Dave Peterson
0
petersod (12005)
2/11/2006 3:30:57 AM
Hi Dave,

Thanks for the suggestion.

Here's what the sheet data looks like that I am trying to get the
information from...

1/30/2006	16:30	9	1
1/30/2006	17:00	11	1
1/30/2006	17:30	12	5
1/30/2006	18:00	9	2
1/30/2006	18:30	0	0
1/31/2006	6:00	0	0
1/31/2006	6:30	1	0
1/31/2006	7:00	5	1
1/31/2006	7:30	9	3
1/31/2006	8:00	10	1
1/31/2006	8:30	13	0
1/31/2006	9:00	10	1
1/31/2006	9:30	14	1
1/31/2006	10:00	12	1


I'd like to look up the date value but also lookup the time value and
only return the value in the 3rd column if both the time and date
match.  The above items are just a sample of what I have, the sheet
lists more days and repeats the times in each day.

So: if date matches 01/31/2006 and time matches 7:00 the value in
column 3 is returned of 5.

I guess I need to better understand the INDEX and MATCH formulas.  I
tried to enter this into Excel but don't quite understand why it's not
working.

I'll try to breakout the formulas to see if it makes more sense.

Alex

0
2/11/2006 3:52:13 AM
Hi Dave,

I've got it!  Your formula was perfect!

Thanks,
Alex

0
2/11/2006 5:22:42 AM
One strange thing:

All work fine except for 11:30 AM.

Any thoughts why?

0
2/11/2006 6:03:22 AM
This often happens with time, it is due to arithmetic precision.

Try this alternative

=INDEX($C$1:$C$100,MATCH(1,(J1=$A$1:$A$100)*(TEXT(J2,"hh:mm")=TEXT($B$1:$B$1
00,"hh:mm")),0))

or even

=INDEX($C$1:$C$100,MATCH(1,(J1=$A$1:$A$100)*(TEXT(J2,"hh:mm")=TEXT($B$1:$B$1
00,"hh:mm")),0))

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

<alexfflores@gmail.com> wrote in message
news:1139637802.333432.59940@g14g2000cwa.googlegroups.com...
> One strange thing:
>
> All work fine except for 11:30 AM.
>
> Any thoughts why?
>


0
bob.phillips1 (6510)
2/11/2006 9:43:33 AM
I've tried to apply this to some work I'm doing and I get #NA.  This i
my data:

SSN           Name       Source    Amount
999999999  John Doe    1            356.35

This is the formula I'm using:

{=INDEX(Sheet1!D1:D1253,
MATCH(1,(A5=Sheet1!A1:D1253)*(C5=1),0))}

Any help is greatly appreciated

--
Chris Berr
-----------------------------------------------------------------------
Chris Berry's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3616
View this thread: http://www.excelforum.com/showthread.php?threadid=51128

0
7/10/2006 3:21:15 PM
I've tried to apply this to some work I'm doing and I get #NA.  This i
my data:

SSN           Name       Source    Amount
999999999  John Doe    1            356.35

This is the formula I'm using:

{=INDEX(Sheet1!D1:D1253,
MATCH(1,(A5=Sheet1!A1:A1253)*(C5=1),0))}

Any help is greatly appreciated

--
Chris Berr
-----------------------------------------------------------------------
Chris Berry's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3616
View this thread: http://www.excelforum.com/showthread.php?threadid=51128

0
7/10/2006 3:22:33 PM
Is there a trick to getting ISNA to work with array formulas?  This i
what I have.

{=if(isna(INDEX(Sheet1!$D$1:$D$1253,
MATCH(1,(A5=Sheet1!$A$1:$A$1253)*(Sheet1!$C$1:$C$1253=2),0))))

--
Chris Berr
-----------------------------------------------------------------------
Chris Berry's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3616
View this thread: http://www.excelforum.com/showthread.php?threadid=51128

0
7/10/2006 3:34:31 PM
Try:
{=INDEX(Sheet1!D1:D1253,
MATCH(1,(A5=Sheet1!A1:A1253)*(C5=1),0))}

HTH
--
AP

"Chris Berry" <Chris.Berry.2aqkaz_1152545653.7526@excelforum-nospam.com> a 
�crit dans le message de news: 
Chris.Berry.2aqkaz_1152545653.7526@excelforum-nospam.com...
>
> I've tried to apply this to some work I'm doing and I get #NA.  This is
> my data:
>
> SSN           Name       Source    Amount
> 999999999  John Doe    1            356.35
>
> This is the formula I'm using:
>
> {=INDEX(Sheet1!D1:D1253,
> MATCH(1,(A5=Sheet1!A1:A1253)*(C5=1),0))}
>
> Any help is greatly appreciated.
>
>
> -- 
> Chris Berry
> ------------------------------------------------------------------------
> Chris Berry's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=36165
> View this thread: http://www.excelforum.com/showthread.php?threadid=511280
> 


0
ardus.petus (319)
7/10/2006 3:40:57 PM
Thanks I got it to work with:

{=INDEX(Sheet1!$D$1:$D$1253,
MATCH(1,(A5=Sheet1!$A$1:$A$1253)*(Sheet1!$C$1:$C$1253=2),0))}

Is there a trick to getting ISNA to work with Array Formulas?

=if(isna(INDEX(Sheet1!$D$1:$D$1253,
MATCH(1,(A5=Sheet1!$A$1:$A$1253)*(Sheet1!$C$1:$C$1253=2),0)),INDEX(Sheet1!$D$1:$D$1253,
MATCH(1,(A5=Sheet1!$A$1:$A$1253)*(Sheet1!$C$1:$C$1253=2),0)))

--
Chris Berr
-----------------------------------------------------------------------
Chris Berry's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3616
View this thread: http://www.excelforum.com/showthread.php?threadid=51128

0
7/10/2006 3:53:57 PM
There isn't any trick, but you don't need to do that much work.

You would only have to check to see if there was a match:

=if(iserror(match(1,(...)*(...),0)),"Not found",
  index(...,match(1,(...)*(...),0))

It'll make the formula just a bit smaller.

Another option would be to use multiple cells.  Put the formula in one, then
put:

=if(iserror(x99),"Not Found",x99)

You could hide that intermediate column that contains the "real" formula.



Chris Berry wrote:
> 
> Thanks I got it to work with:
> 
> {=INDEX(Sheet1!$D$1:$D$1253,
> MATCH(1,(A5=Sheet1!$A$1:$A$1253)*(Sheet1!$C$1:$C$1253=2),0))}
> 
> Is there a trick to getting ISNA to work with Array Formulas?
> 
> =if(isna(INDEX(Sheet1!$D$1:$D$1253,
> MATCH(1,(A5=Sheet1!$A$1:$A$1253)*(Sheet1!$C$1:$C$1253=2),0)),INDEX(Sheet1!$D$1:$D$1253,
> MATCH(1,(A5=Sheet1!$A$1:$A$1253)*(Sheet1!$C$1:$C$1253=2),0))))
> 
> --
> Chris Berry
> ------------------------------------------------------------------------
> Chris Berry's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36165
> View this thread: http://www.excelforum.com/showthread.php?threadid=511280

-- 

Dave Peterson
0
petersod (12005)
7/10/2006 4:51:05 PM
Reply:

Similar Artilces:

VLOOKUP wonkiness
So a friend asked for help with VLOOKUP. I told him what to write. and it doesn't work. But I think it should. We have column A full of values (hundreds of work ids) and a list in column C of the jobs my buddy has (his 20 or sounique work ids). He wants to use the second column to quickly lookup up those in teh first that are his. I said to use in Column B the formula =VLOOKUP(A1,$C$1:$C$21,1) to put data in beside each job that's his. I had him put a '-' in C21 to fill in all the non matches. When he said it didn't work, I made a mockup that worked. So I had him se...

Multiple file problem
I have just transferred my old pst file to a new computer. What Outlook 2003 has done is make multiple Contact and Calendar files of each. Some have the original data in, but most are empty. I need to know how to get back to the one file of each. The OK files are in \\Personal Folders. The empty ones are in something like \\Personal Folders\Draft\Draft\Draft\ ......... There are probably 100 od the draft ones set up in the system, and they don't appear to be deleted when I do a manual delete. HELP PLEASE! Rod Rod <rodlg000@yahoo.com> wrote: > I have just transferred my o...

Update to Access Multiple tables via VBA
I'm somewhat familiar with updating from Excel to Access via VBA. How can I update to multiple tables in Access that have a one to many relationship using VBA. Table are linked via key. tbl_One is one Many with tbl_Two via Foreign Key tbl_One is one Many with tbl_Three via Foreign Key Set db = OpenDatabase("C:\LinkedTest\LinkTestDB.mdb") ' open the database Set rs = db.OpenRecordset("tbl_One", dbOpenTable) With rs ..AddNew ' create a new record ' add values to each field in the record .Fields(&...

Alowing for the Absence of Data from Multiple cells?
In my spreadsheet, I have those annoying #/DIV0! errors show up until enter data in the supplying precedents. I found a great tip but i only described a solution when there was one cell providing input t the calculating cell. I have cells that depend on 7 or more predents. Any help is appreciated -- Message posted from http://www.ExcelForum.com Juan, You could try =IF(ISERROR(my_formula),"",my_formula) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Juan >" <<Juan.1...

Adding Multiple Variables
I am using the following script to delete shares and want to specify multiple shares, instead of just one share name. How can I do so? DomainName = "domain.com" ServerName = "Server1" ShareName = "Share1" Set cont = GetObject("WinNT://"& DomainName &"/"& ServerName &"/LanmanServer,FileService") cont.Delete "FileShare", ""& ShareName &"" Also, how can I add to this script so that it deletes the directories associated witht he shares, once the shares are re...

Missing values from a pivot table field
I am creating a pivot table from an access database containing 896426 records. The issue I am running into is that on one of the fields the pivot table is only pulling in one of two values, but the access table does contain the second value. When I click on the arrow associated with that field it does not contain the second value either. Is there somewhere else in the settings this value could have been lost? Perhaps try Drag the field off the table Refresh the table Drag the field back on. With pivot tables you need to let us know which XL version you have. pivot ta...

how to divide the number in one cell into multiple cells
I have a value (5000) in one Excel cell. I need to distribute it into equal values in 10 cells. Can any one please let me know how to do that Suppose you have 5000 in cell A1 and your target cells are B1:B10. Select B1:B10. Keeping the selection of 10 cells press = to enter a formula =A1/10 Instead of enter press Ctrl+Shift+Enter...Now try changing the value in A1 If this post helps click Yes --------------- Jacob Skaria "Ramya" wrote: > I have a value (5000) in one Excel cell. I need to distribute it into equal > values in 10 cells. Can any one ple...

PO Value against actual
Can RMS show what was the original value and quantity of the PO as against the recieved value and quantity? Sometimes there are some descrepancies between the actual PO and what was delivered. Does RMS record the difference and show it as well? ...

Subform comparison of 2 tables excluding non zero value
I am creating a subform (Access 2003/NT) in which the user will type in a Melt# (contained in the tblChemicalResults). I need to create a side by side comparison of the chemical requirements from the tblChemicalRequirements to the actual results contained in the tblChemicalResults, but I need it to exclude any non zero values from both tables. Each table contains all of the possible 14 different metal elements that could possibly go into making a particular part. Obviously, not all 14 go into every part, so how do I display only the elements that have a non zero value while also display...

conditional formatting: multiple conditions with min
Hello, I am using Excel 2007 and I am having an issue with conditional formatting. I have 2 columns of data that I'm working with: Column "c" which contains both names and some empty cells, and column "i" which contains dates and some empty cells. If there's a name in column c then there is definitely a corresponding date in the same row in column i. However, there are sometimes blank cells in column c dates that have dates in column i and some rows that have blanks in both c and i. See the following example: Sue --- 10/1/2010 ___ --- 10/1/2010 Joe --- 10/23/2010 ...

Calculating percentages of number of occurence of a text value
I have a field that is called txtpurpose. The value of this field in any record could be 4 different values " Re-financing", "Corporate Finance", "Buy- out", or "Restructuring" For a particular month (a field txtmonth) I want to count how many records had each of these values and what percentage that number was of the total number or records for that month. So I want to end up with a reuslt like this: Re-financing 46% Corporate Finance 38% Buy-out 12% Restructuring 4% Total 100% Can someone start me off in the right direction please? Many ...

Sharing one exchange for multiple (small) company.
Hi, I need to share a new Exchange 2003 Standard (not Enterprise) for 3 organisation. Same information store, same mailbox limits, ... Each one will have its own syntax for smtp adresses (xxx@first.com , yyy@second.com, zzz@third.com). I will organise all in OU, at least one per organisation. How can I affect smtp adresse according to its location is AD (OU) ? Other question : how can I have an adresse book per organisation ? One have not to see people of the two others. Thanks in advance. Sam.G. ...

Copy to specified sheet, values only
Good morning I hope someone can help me with this. I am currently using the following code to copy the entire data from one sheet, and paste it below the previous data in a different sheet: Set srcsht = Sheets("Working") Set dstsht = Sheets("All Trades") LastrowA = srcsht.Cells(Cells.Rows.Count, "A").End(xlUp).Row LastrowB = dstsht.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1 srcsht.Range("A1:A" & LastrowA).EntireRow.Copy dstsht.Cells(LastrowB, 1) However, I need the macro to only paste the values, not the formulas, data...

Merge data from multiple worksheets
I am using excel 2000 and have 4 worksheets in the same workbook. Each worksheet is formatted with a yearly calendar. This is a vacation calender for 4 different positions in my department. All 4 worksheets are exactly the same except for the names of the people on vacation in the cells. I would like to have a way to combine these 4 worksheets into one without the lengthy process of creating a 5th sheet with formulas in each cell inhabitted by names. I would prefer to have a seperate workbook that was linked in some way. All suggestions are appreciated. Hi, Not knowing what your detail calen...

Null value to "0" / Currency format
I have a crosstab query in which my columns, based on [BidType], give totals based on the field LineTotalSellPrice. It returns the totals properly with the expression: LineTotalSellPrice: Sum(([Quantity]*([UnitPrice]+[QuoteCost]))*[Markup]) ...however in the instances where nothing exists, it returns Null, or blank. I'd like to convert this to a currency value of $0.00, so I changed my expression to: LineTotalSellPrice: Nz(Sum(([Quantity]*([UnitPrice]+[QuoteCost]))*[Markup]),0) ...and it returns the proper value, but not in a currency format (even though the field's format proper...

Conditional Formatting: comparing cell values
Using MS 2007, I have a column that has 40 sets of three rows; the rows are for three seperate years for 40 different schools that are included in the chart. The cells I am workign with show the performance indicator - a number - for each of the three years for the school in that set. I need to compare the three numbers for each set and show which set of three shows gains, and eventaully determine the highest performer over the three years. Sample: # SCHOOL YEAR 60 AVE 2008-09 61 AVE 2007-08 53 AVE 2006-07 69 BCE 2008-09 53 BCE 2007-08 56...

Multiple recipients at one address
Is there a way to tell the database to print a different header for an address label if there are multiple members at the same address? I have queried a mailing but in order to conserve funds the members only want one sent when they are at a single address. At the same time I have to keep them as unique entries because we keep track of birthdays, etc. We would like to keep the names on the labels of the members who live alone. Any help is appreciated. There is a generic concatenate function with usage at http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. You could us...

Multiple accounts problem #2
Hi! I have multiple accounts configured in MS Outlook 2003. My main accoun is a 3rd party MAPI account (Kerio Outlook Connector) and then I hav some IMAP and POP3 accounts as well. Ever since I installed Service Pack 1 for MS Office 2003, every mail send, gets sent from my main account, regardless which account I selec in the "Accounts" pop-up menu. This effectively means that I cannot sen any e-mail from any account other than the main account. Before installed sp1 I could send messages from all accounts. I hav considered the option to reinstall Outlook without the service pack...

Checking the Value of a Check Box in a Macro
I have a macro that hides a named range on rows. I also have a check box. If the check box is checked I want an error message to display and tell the user to clear the check boxes before teh rows will hide. ' ' Hide Spec_Plu_22_10_00 ' Sub Hide_Spec_Plu_22_10_00() 'Unprotect Time Sheet ActiveSheet.Unprotect If CheckBox135 = True Then MsgBox "Uncheck boxes in spec section your trying to close.", vbInformation, "Alert Message" GoTo 300 Else GoTo 200 End If 'Hide Rows 200 Range("Plu_22_10_00").Selec...

Timesheet cube negative values Project Server 2007
Hi everyone, We are using Project Server 2007 SP2. When I generate a report from the Timesheet cube (in Excel or Data Analysis) to get the working billable and non-billable hours per period, per task, per resource I get negative values in the report. So far we found out the reason for the negative values is the correction by the timesheet manager. However the corrected hours in the timesheet are different than the hours generated in the report. Has anyone had any issues with this? As we are struggling with this for a couple of days now I will very much appreciate any help. ...

Working with multiple worksheets
This is probably very simple but i am struggling with this. I have a workbook where one sheet will be updated on a monthly basis and i want the sheet preceding it to capture its outputs. A few things i need it to capture is to check if Col B has a specific value and if yes check the value of the cell adjacent to it and count this if it is a Yes or a No. Please help! BK It sounds like VLOOKUP will work for you. Look in the help index for VLOOKUP. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Balaji K. Arige" <v_2balar@hotmail.com> w...

After installing multiple updates, error code 0xC80001F
I have been installing multiple updates (due to computer restore). During this last set of updates, I noticed that there were a few that said they weren't installed (failed) successfully. I went back to the installed updates info, but there were no items that showed "failed". When I tried to go back to the Windows Update Home page, I received this error code [Error number: 0xC80001FE]. I do not have a 3rd party anti-virus or firewall active right now. I have done nothing following the installation of updates. -- Real integrity is doing the right thing, knowing tha...

Calculate to another cell as default value
Hi. Given the following: A B C 1 5 9 __ 2 8 10 __ I'd like for the user to enter values in A and B and have Excel display B minus A in C as a DEFAULT value. That is, for row 1, once the user enters 9 in B, I want to display 4 in C but give the user the option to override the calculation. A formula in C won't work because the user would delete the formula if he overrides it. Thanx. I don't Why not use an extra column (C) and have this formula in D: =IF(C1="",B1-A1,C1) -- Kind regards, Niek Otten Microsoft MVP - Excel "...

Inventory Value
what even old programs like Cougar has, RMS needs to address. The true value of inventory based on either a LIFO or FIFO method. Year end inventories for tax purposes needs to be more acurate than "last Cost" or "Weighted Average". Average weighted cost is the accepted standard for retail. LIFO-FIFO is impractical for most retail businesses. "Marc Cotton" <Marc Cotton@discussions.microsoft.com> wrote in message news:0BC94A17-6BB8-4DA6-A8DE-EBE91596BB6B@microsoft.com... > what even old programs like Cougar has, RMS needs to address. The true &...

Cell Values
I would like to return the value of another cell(C1) if a cell has a value in it, otherwise I want it to return a value of 0. Example: IF(A1="any value",C1,0) This formula would be written in B1. "Any value" would be numbers or text. Thanks 4 your help!!! Try: =IF(A1<>"",C1,0) or =IF(ISBLANK(A1),0,C1) Good Luck, Mark Graesser "GaryW" <gary.wicker@acadiapolymers.com> wrote in message news:040b01c34721$41fe8d00$a301280a@phx.gbl... > I would like to return the value of another cell(C1) if a > cell has a value in it, otherwise I...