Search in multiple columns using VLOOKUP?

Hi,

I'm looking for a formula to find a row in a spreadsheet with two matching
fields which returns the value of a field in a certain column in that row.
VLOOKUP only supports searching in one column as far as I know. Let me give
you an example to clarify my request.

Please read the following text in a non-proportional font like Courier New.

Worksheet A

A  B  C  D
0  4  7
0  3  6
1  0  7
5  0  6

Remarks: either column A or B is always zero, and the other one non-zero. I
need the formula to use the value of the field which is non-zero. Further
on, the value of the column C has to match. We are searching for the value
that needs to be displayed in column D. We are searching in Worksheet B,
which looks like this for example:

Worksheet B

A  B  C  D
0  4  3  1
0  4  7  2
0  4  8  3
0  3  3  4
0  3  7  5
0  3  6  6
1  0  3  7
1  0  7  8
1  0  8  9
5  0  6  10
5  0  4  11
5  0  9  12

Row 1 in worksheet A has cell A=0, so we are searching in worksheet B for a
row where the B column equals 4, AND the C column equals 7. So the formula
has to return the value of the D column of the second row, which is 2.

Row 2 in worksheet A has cell A=0, so we are searching in worksheet B for a
row where the B column equals 3, AND the C column equals 6. So the formula
has to return the value of the D column of the sixth row, which is 6.

Row 3 in worksheet A has cell A=1, so we are searching in worksheet B for a
row where the A column equals 1, AND the C column equals 7. So the formula
has to return the value of the D column of the eigth row, which is 8.

Row 4 in worksheet A has cell A=5, so we are searching in worksheet B for a
row where the A column equals 5, AND the C column equals 6. So the formula
has to return the value of the D column of the tenth row, which is 10.

So with the correct formula for the D column in worksheet A, worksheet A
will look as follows:

Worksheet A

A  B  C  D
0  4  7  2
0  3  6  6
1  0  7  8
5  0  6  10

Could anyone please provide me with the formula for the D column?

Thanks a lot!

George


0
gtog (9)
1/21/2004 9:42:49 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
820 Views

Similar Articles

[PageSpeed] 42

Hi George

You received a reply to this yesterday. Have you tried it?. Just to
repost my yesterday's comment:
------
first have a look at
http://www.mvps.org/dmcritchie/excel/vlookup.htm
(search for double lookup)

for your specific problem use the following
=IF(A=0,INDEX('sheet b'!$D$1:$D$99,MATCH(1,('sheet
b'!$B$1:$B$99=B1)*('sheet b'!$C$1:$C$99=C1),0)),[fill in if A<>0])

-------

In respect to your example the formula should look like the following:
=IF(A=0,INDEX('sheet b'!$D$1:$D$99,MATCH(1,('sheet
b'!$B$1:$B$99=B1)*('sheet b'!$C$1:$C$99=C1),0)),INDEX('sheet
b'!$D$1:$D$99,MATCH(1,('sheet
b'!$A$1:$A$99=A1)*('sheet b'!$C$1:$C$99=C1),0)))

not fully tested but it should work. If you have any firther problems
you can mail me your spreadsheet and I'll have a look at it

HTH
Frank


George wrote:
> Hi,
>
> I'm looking for a formula to find a row in a spreadsheet with two
> matching fields which returns the value of a field in a certain
> column in that row. VLOOKUP only supports searching in one column as
> far as I know. Let me give you an example to clarify my request.
>
> Please read the following text in a non-proportional font like
> Courier New.
>
> Worksheet A
>
> A  B  C  D
> 0  4  7
> 0  3  6
> 1  0  7
> 5  0  6
>
> Remarks: either column A or B is always zero, and the other one
> non-zero. I need the formula to use the value of the field which is
> non-zero. Further on, the value of the column C has to match. We are
> searching for the value that needs to be displayed in column D. We
> are searching in Worksheet B, which looks like this for example:
>
> Worksheet B
>
> A  B  C  D
> 0  4  3  1
> 0  4  7  2
> 0  4  8  3
> 0  3  3  4
> 0  3  7  5
> 0  3  6  6
> 1  0  3  7
> 1  0  7  8
> 1  0  8  9
> 5  0  6  10
> 5  0  4  11
> 5  0  9  12
>
> Row 1 in worksheet A has cell A=0, so we are searching in worksheet B
> for a row where the B column equals 4, AND the C column equals 7. So
> the formula has to return the value of the D column of the second
> row, which is 2.
>
> Row 2 in worksheet A has cell A=0, so we are searching in worksheet B
> for a row where the B column equals 3, AND the C column equals 6. So
> the formula has to return the value of the D column of the sixth row,
> which is 6.
>
> Row 3 in worksheet A has cell A=1, so we are searching in worksheet B
> for a row where the A column equals 1, AND the C column equals 7. So
> the formula has to return the value of the D column of the eigth row,
> which is 8.
>
> Row 4 in worksheet A has cell A=5, so we are searching in worksheet B
> for a row where the A column equals 5, AND the C column equals 6. So
> the formula has to return the value of the D column of the tenth row,
> which is 10.
>
> So with the correct formula for the D column in worksheet A,
> worksheet A will look as follows:
>
> Worksheet A
>
> A  B  C  D
> 0  4  7  2
> 0  3  6  6
> 1  0  7  8
> 5  0  6  10
>
> Could anyone please provide me with the formula for the D column?
>
> Thanks a lot!
>
> George


0
frank.kabel (11126)
1/21/2004 9:53:27 AM
There is probably a better way of doing this, but you cuold perhaps try
the following:

If you enter a further field which concatenates the values in a,b and c
in worksheet A and does likewise to the values in the lookup table in
worksheet B then you can always use a simple lookup function to return
the correct row into column D of worksheet A.

e.g. field in both worksheets that is:

=concatenate(a1,b1,c1)

if you put this in column e, and in column f, then make a the lookup
table based in columns e and f the value of the row then d could
equal:

=vlookup(e1,Table in Worksheet B,2,0)

This depends if its okay to enter the extra columns.

I think!!

DF.


---
Message posted from http://www.ExcelForum.com/

0
1/21/2004 10:05:41 AM
Hi Frank,

Thanks for your reply. I will check this out. I posted a similar question
yesterday using Google, but couldn't find it anymore, so I posted this
version.

George


0
gtog (9)
1/21/2004 3:00:38 PM
Thanks DF, I will check this out.

George


0
gtog (9)
1/21/2004 3:02:14 PM
Reply:

Similar Artilces:

Another multiple criteria/column question
Ok, first post and pretty much a new user to Excel. I have two sheets that I am working with, trying to recall data from one to the other that meets criteria. In a nutshell: Sheet 1 contains a column of unique values (col A), cols C,D, and E are where I want to insert the formula to find data on Sheet 2. Sheet 2 contains 4 columns, A contains multiple occurrences of each value (from Sheet1, column A), each with its own timestamp in column D. On Sheet1, in column C, I want to find a value on Sheet2 in column A and return the timestamp in column D. I know I can use VLOOKUP for this. =VLO...

Controlling printed records when report bound to multiple tables
I created a report that uses the control from a form to generate a report based on that record's primary key. This form also has a subform which has relationships tied to the primary key for record identification and is linked to the main table. When preview the report the data from the subform either does not show up in the preview when using the filter [control]=[form]![control].[value] or makes multiple copies of the report equal to the number of entries in the subform's table. Is there any way around this? I have tried queries but have not found a way to use a f...

How to shift address listings from row list to columns?
I have a mailing list with name, address, city, state & zip with each item in individual rows like a list of labels and a few empty rows of space between each listing. How do I create/transfer this list into columns accross so I can sort by city or zip? Thank you! If your data is nicely grouped, with each group in 5 lines, viz: name add city state zip then an earlier suggestion given which worked might be worth a try: See: http://tinyurl.com/wgcb -- hth Max ----------------------------------------- Please reply in newsgroup Use xdemechanik <at>yahoo<dot>com for email --...

setting up multiple email accounts #2
I followed the rules for setting up the pop3 and smtp accounts for multiple mail accounts, but when I access outlook express the additiona account does not recognize the password I've asked it to remember. HELP! KCB <anonymous@discussions.microsoft.com> wrote: > I followed the rules for setting up the pop3 and smtp > accounts for multiple mail accounts, but when I access > outlook express the additiona account does not recognize > the password I've asked it to remember. HELP! Ask in an Outlook Express newsgroup. -- Brian Tillman ...

Excluding hidden columns and rows when copying to another workbook
When I print part of a worksheet that has hidden columns and rows - the hidden columns and rows do not print. That's what I want. Now--I'd like to take that same data and copy it to another workbook excluding the formulas and hidden columns and rows so that the new file contains only the data as was printed. How can I do that? PJ Select your range including hidden rows and columns then Edit>Go To>Special>Visible cells only>OK Now do your copy/paste. Gord Dibben Excel MVP On Fri, 6 Feb 2004 10:16:07 -0800, "PJ" <anonymous@discussions.microsoft.com> wr...

Sorting with Column has Formula
Hi everyone, I never imagined that the formula in the column would affect the sorting order in any way, but it does in my case. Below is the formula in that I have in Col I, and I'd like to sort it in Ascending order, but the result is that it sorts with all the empty rows on top and the one with the result from the formula at the bottom. I assume it consider the "I" in the "IF" function in the formula, but I'm not sure. Can anyone tell me how to fix this please? =IF(E2="","",IF(J2="X","Priority #1",IF(...

Permissions is not allowing me to use my print preview.
Help! Don't understand My Permission in Excel. Even though I unrestrict, it still doesn't allow me to Preview my documents. How do I reset? Please Help, ...

Search criteria
I have a search form that selects records from a query when the value of two fields (txtmonth-a date field and txtcompnay- a text field) in the query match the value of two controls (txtsearch and txtsearch2) on my form. However I want to change the code so that the serach criteria works like this: Find records where: txtsearch=txtcompany and txtsearch2 is blank txtsearch2=txtmonth and txtsearch is blank txtsearch=txtcompany AND txtsearch2=txtmonth Here is my current code: Private Sub txtSearch_KeyPress(KeyAscii As Integer) If KeyAscii = 13 Then 'execute search ...

How to get TASK_ID field for summary tasks without using Project.a
I know for tasks which are not summary tasks we can get TASK_ID field using statusing web service. But i could nto find any other options than Project web service to get TASK_ID field for summary tasks and the top level project task. Problem of using Project web service is that in my custom sharedpoint web part where we are using PSI web services we get all the data required using Resource and Statusing web service for the logged in resource. But Statusing web service retrieves TASK_ID only for actual tasks and not for summary tasks. Now just to get TASK_ID of summary tas...

Question on multiple NICs used by Exchange 2003
I have a requirement to bind four virtual SMTP servers to unique IP addresses. The server has four NICs and each IP address is uniquely bound to a virtual SMTP server. I used telnet to confirm that the appropriate virtual SMTP server responds to each assigned IP address. In case you are wondering why I did not use virtual IP addresses- Per Microsoft, the virtual IP address schema will not satisfy our requirement that the same IP address be used for sending (relaying) the e-mail that received it. Their recommendation was to have a NIC for each virtual SMTP server. The problem that I am ex...

How to search against XML files in the file system?
Putting the search textbox on the page is the easy part. What's preferred way to find terms in XML files located on the file system? Like finding stuff saved in XML files some of the blogs use these days to store their blog items? There can be lots and lots and lots of XML files on the file system to search. -- <%= Clinton Gallagher NET csgallagher AT metromilwaukee.com URL http://clintongallagher.metromilwaukee.com/ clintonG wrote: > Putting the search textbox on the page is the easy part. What's preferred > way to find terms in XML files located ...

Varying column widths
I have 2 excel sheets - one is 20 columns wide the 2nd is 7 columns wide. The 20 column sheet has a general information section at the top that I would like to add to the 7 coulmn spreadsheet. However, when I copy and paste it "spreads out" or goes way beyond the width due to the number of columns. I have tried paste special object and entering the excel sheet that way and it doesn't fit properly and too many cells are shown. Any other way to do this? If you only want it up there for appearance purposes, you can try: Copy the selection. Click the cell where you want t...

retain only rows with condition that cells in column H containing "AU"
I have a large database containing columns with one column specifies the product name. In one instances, I need to retain only rows with condition that cells in column H containing "AU" of the entire string in the cell. Delete those rows without it. The problem is , that the AU of the string can appear in any position, not a fixed position. Is there any simple way of doing it? Many Thanks! Regards, Bora Hi, You can use AutoFilter. Choose the command Data, Filter, AutoFilter. Then open the autofilter drop down in the column H and choose Custom and from the first drop down, t...

2007
Project desktop expert, new to Project Server 2007 and working with a sandbox implementation currently. When I use the PWA Build Team...Replace to replace a generic resource on a project with an actual named resource after first publishing a plan, my updates don't appear to be reflected in Project Server. If I check out and open the Project Plan in MSP Pro, however, the updates were made, and then if I publish the schedule, the updates appear in server. I thought when you made resource updates using PWA that they should be reflected immediately (or as soon as the request...

Developing a robust database at one co. then using it at another c
Are there any legal implications of using the type of database listed above to perform similar functions for another company? The 2 company's are in 2 totally different industries and the new database will have to be modified to fit the requirements for the industry it is in. The database was developed at the old company. there is no such thing as a robust Jet database. Move to SQL Server if you want to build a solution that will work for the next decade. Jet is and always has been depecrated On Apr 7, 12:14=A0pm, BoaMan10 <BoaMa...@discussions.microsoft.com> w...

Equivalent Column Break
In MSWord you can insert a column break when doing newspaper columns. If I have two text frames connected, and I am almost at the bottom of one but want Publisher to start at the top of the next one, how do I insert a 'break'? or do I have to press return until it move the text. Thanks Anita (1) You could shorten that column so that your text breaks where you want it to. (2) Or you could check the Help file and search for "insert break". Insert a column break You can insert a column break anywhere in a text box. If the text box contains more than one column, the ...

How do I create a summary page from multiple worksheets
Trying to roll-up information from multiple worksheets within the same workbook to a summary page. These worksheets are copies of each other. For example: each worksheet has a column labeled "defect number". The users can record multiple defect numbers within a cell (e.g. 897, 992, 1001) So sheet1, row1 = 897, 990 sheet2, row1 = 992 sheet3, row1 = 995, 1001, 1012 sheet4, row1 = empty How do I (or can I) rollup this information to a summary page where sheet5 is the summary worksheet and row1 = 897, 990, 992, 995, 1001, 1012. Here's what I have so far [=Sta...

stationary, templete, signatures with multiple accounts
Hello all, I use Outlook 2002 to access three email accounts ( Work, Hotmail, and home ISP ) Obviuosly I can choose from which account I'm sending from at the time of mesasge creation. What I am asking is, is it possible for when, let's say, I choose to send from my work account, a certain stationary and/or template and/or signature is used ----- and a different stationary and/or template and/or signature is used when I send from my ISP, etc....... Please advise, preferably be sending directly to patrick072040@comcast.net., or by replying to this post. Thank you in advance !! O...

Using the classes created with xsd.exe
I have created classes from several xsd files. These files create about 150 classes and spot checking them they do represent types in the xsd files. the question is how do I use these files. How do I load data into them and create xml from them. Is there some articles about this subject. Thank you, -- Jerry Hi Jerry, As for the classes you've generated, are they normal classes or dataset classes? As for the normal classes you generated through xsd.exe, you can use XML serialization to convert those class instances into XML content or deserialize the XML content back into objec...

Hiding Column and Row Bars.
I know theres a way to costumize the way you view an excel page by hiding toolbars, but is there a way to hide the rows and column bars just so all you can see is the actual page. E.G. is there a way to Hide the top bar that defines the colums "A", "B", "C","D" and Rows 1,2,3,4,5. Go to Tools>Options>View, there are a number of options you can play with there. -- HTH RP (remove nothere from the email address if mailing direct) "tamato43" <tamato43@discussions.microsoft.com> wrote in message news:7D1E5DEB-D6F2-47C8-95E2-2...

Column comparison
I am trying to compare 2 columns of numbers so that I can identify and delete numbers no longer required. Can anyone help me find a formula for this please? Many thanks DT Hi Dave, Need more information like a sample of the existing data plus a sample of what you want left. Maybe an explanation of the criteria for what needs to be deleted. -- Regards, OssieMac "Dave T" wrote: > I am trying to compare 2 columns of numbers so that I can identify and > delete numbers no longer required. Can anyone help me find a formula for > this please? > > Many than...

Disallowing duplication of nmbers in a column
Is Excel capable of disallowing the same numbers in a column? I have a column in a sheet that invoice numbers are entered into. I would like that column to alert or something if duplicate numbers are typed in. Hi Have a look here: http://www.cpearson.com/excel/NoDupEntry.htm -- Andy. "Barb1" <Barb1@discussions.microsoft.com> wrote in message news:E3631DD3-724E-4C0D-956B-4201876A9A4A@microsoft.com... > Is Excel capable of disallowing the same numbers in a column? I have a > column in a sheet that invoice numbers are entered into. I would like > that > c...

How to use AjaxToolKit in asp.net for DHTML Editor
Dear Sir/Madam Please tell me how to use AjaxToolKit dll for DHTML purpose using C#. Thanks in Advance "Deep" <vinodkus@gmail.com> wrote in message news:7ac633e9-2cbd-4417-9b4e-363a9b5b3e74@d27g2000yqf.googlegroups.com... > Please tell me how to use AjaxToolKit dll for DHTML purpose using C#. http://tinyurl.com/ybfuqo8 ...

multiple balances on one printout
I want to print all the months on one sheet. with a total, is this possible and if so how do I do so. Thanks ...

How to merge columns and rows into one cell besides using Merge and Center Icon?
I had posted this question before, but I couldn�t find this thread in any of the pages up till page 17. So I�m posting again. I want to merge all rows and columns starting from A1 to J2, with no lines in between into one cell. My text data value are in D1 and D2 respectively. Using Merge and Center Icon will only retain the upper-left most data, resulting the data in D2 to be deleted. So how to merge all rows and columns into one cell and yet prevent the data in D2 to be deleted? Any help will be greatly appreciated. ------------------------------------------------ ~~ Message posted from h...