VLookup?????

=IF(ISNA(VLOOKUP(B2,'TableB'!$Y$2:$Y$100,1,FALSE)), "",Column A in
tableB) 


This is the code i have. 

I want to search tru all column B in table A. If i find an identical
value in table B i want the field in table A to show the corresponding
field in Column A in table B. 

Also if there is 2 occurences of column B in table B, i want the field
in table A to show both values. 



Any ideas? (if u can understand what im asking)


-- 
Hazy
------------------------------------------------------------------------
Hazy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33654
View this thread: http://www.excelforum.com/showthread.php?threadid=534280

0
4/19/2006 6:10:20 PM
excel 39879 articles. 2 followers. Follow

2 Replies
756 Views

Similar Articles

[PageSpeed] 47

=IF(ISNA(MATCH(B2,Tableb!$Y$2:$Y$100,0)),
"",INDEX(Tableb!$Y$2:$Y$100,MATCH(B2,Tableb!$Y$2:$Y$100,0)))
will return value in column A

If there are several occurences, use Data>Filter>AutoFilter or Advanced
Filter

HTH
--
AP

"Hazy" <Hazy.26ix2z_1145470501.2426@excelforum-nospam.com> a �crit dans le
message de news:Hazy.26ix2z_1145470501.2426@excelforum-nospam.com...
>
> =IF(ISNA(VLOOKUP(B2,'TableB'!$Y$2:$Y$100,1,FALSE)), "",Column A in
> tableB)
>
>
> This is the code i have.
>
> I want to search tru all column B in table A. If i find an identical
> value in table B i want the field in table A to show the corresponding
> field in Column A in table B.
>
> Also if there is 2 occurences of column B in table B, i want the field
> in table A to show both values.
>
>
>
> Any ideas? (if u can understand what im asking)
>
>
> -- 
> Hazy
> ------------------------------------------------------------------------
> Hazy's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=33654
> View this thread: http://www.excelforum.com/showthread.php?threadid=534280
>


0
ardus.petus (319)
4/19/2006 6:26:22 PM
=IF(ISNA(MATCH(B2,TableB!$B$2:$B$100,0)),"",INDEX(TableB!$A$2:$A$100,MATCH(B
2,TableB!$B$2:$B$100,0)))

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Hazy" <Hazy.26ix2z_1145470501.2426@excelforum-nospam.com> wrote in message
news:Hazy.26ix2z_1145470501.2426@excelforum-nospam.com...
>
> =IF(ISNA(VLOOKUP(B2,'TableB'!$Y$2:$Y$100,1,FALSE)), "",Column A in
> tableB)
>
>
> This is the code i have.
>
> I want to search tru all column B in table A. If i find an identical
> value in table B i want the field in table A to show the corresponding
> field in Column A in table B.
>
> Also if there is 2 occurences of column B in table B, i want the field
> in table A to show both values.
>
>
>
> Any ideas? (if u can understand what im asking)
>
>
> -- 
> Hazy
> ------------------------------------------------------------------------
> Hazy's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=33654
> View this thread: http://www.excelforum.com/showthread.php?threadid=534280
>


0
bob.phillips1 (6510)
4/19/2006 6:26:56 PM
Reply:

Similar Artilces:

update vlookup formula sheet reference for multiple sheets
In Excel 2003 Starting with this formula I need to have the sheet name change to each tab available on the sheet. =VLOOKUP($A$1,'4-4 to 4-10'!$A:$P,13,FALSE) Other tab names are: 4-11 to 4-17 4-18 to 4-24 4-25 to 5-1 Looking for a more automated way to create the following other than manually change the sheet name. =VLOOKUP($A$1,'4-11 to 4-17'!$A:$P,13,FALSE) =VLOOKUP($A$1,'4-18 to 4-24!$A:$P,13,FALSE) I think I am seeing a possibility to use INDIRECT but not sure how. Or is this VBA stuff? -- SRH I'm not quite sure what you're doing, but ...

Sum Products with VLOOKUP
Is it possible to sum the product of 2 columns, 1 column being the result of a VLOOKUP function, without creating a third column for the individual row result? Here is my problem: Column A contains a list of activities in various countries Column B contains the currency code in which those activities are priced Column C contains the price of the activity in the currency designated in col B A (separate) look up table has the exchange rates to my currency Is there a formula I can use at the bottom of col C to have the total price in my currency? I have tried this formula, but it...

Double VLOOKUP
Hi, Could someone help me out with the following: I would like to lookup IR00202PH in column A and subsequently check if column B contains an "S" and column C contains "FIXED". If both lookups are true, then again lookup IR00202PH in column A and retrieve the corresponding value from column D as a result. If false take the value from column E. Thanks a lot !! Regards, Robert =IF(ISNA(MATCH(1,(A1:A20="IR00202PH")*(B1:B20="S")*(C1:C20="FIXED"),0)), VLOOKUP("IR00202PH",A:E,5,FALSE), INDEX(D:D,MATCH(1,(A1:A20="IR00202PH")*(B1:B...

VLookup in Excel 2007
In Excel 2003, when you looked up in the Criteria table, the identifier you chose had to be on the end and in alpha numeric order. Is this still the case? If I was to vlook up in multible tables and generate my own unique information, Can I go to a table created in the vlookup and use it even if it is not in alpha numeric order? Is there a work around? I don't really understand your question, but the quick answer is that there is no change in Vlookup from 2003 to 2007. You have always been able to use a table that wasn't in order by using False as the fourth parameter....

VLOOKUP #45
I am running a vlookup for part #'s at my company. The table that I have may sometimes contain the same part numbers multiple times. I want my vlookup to look at all of the same part number and give me a total of the column that I'm looking in. For example PART # QUANTITY 15300001 100 15906231 52 15308526 78 15300001 69 Now when I do a lookup on a separate worksheet underneath the part number 15300001 I want my quantity to show 169. Is this possible?...Please help. Thanks in advance. Travis Dahlman ...

Vlookup combo box troubles
I ahve no idea what it is called but will try to explain. I have a vloolup table on one sheet and another with combo dropdown lists used in excel 2003 on my pc at home and on my work pc that when an item is required for selection or entry using the combo drop down boxes, the initial letters of the product entered are searched on by those characters. However, when this excel file is sent to other users, it works on some pcs at work and not others. The combo drop down box will not open up (when i double click the cell to search by characters entered from the vlkup) but only the list box ...

Using Vlookup in Excel
Is there a way to have a lookup value displayed when you starting typing the first couple of letters of the value in the box? Also, I would like to have a calendar in a drop-down to select a date by simply clicking on the date. Can this be done in excel? Tools>Autocorrect options -- Kind regards, Niek Otten "JimD" <JimD@discussions.microsoft.com> wrote in message news:88D6FB87-5B00-4B83-95E9-153872A038DC@microsoft.com... > Is there a way to have a lookup value displayed when you starting typing > the > first couple of letters of the value in the box? >...

Vlookup data wrong if the small value found are same
Dear Friends, I need to find the smallest 8 amounts from hundreds of data and look up the data of the corresponding amount. So I used the small function with referrence to the Rank column and found two -100,000.00 and each of them related to Joey and Keith. However, the vlookup function retrieved both data as Joey and lost Keith's Number and Name. Same problem occurs if retrieving the top 10 amounts with large function. Thanks for your great help in advance. Fanny Rank Amount Number Name 1 -156,250.00 8 John 2 -120,547.00 13 Peter 3 -103,100.00 541 Mary 4 -1...

Vlookup data discrepancy
How do I remove the hidden apostrophy in a cell (see e.g.)? I have tried Find/Replace, changed the Alignment and formatted the cell but failed. e.g. 'alphanumericdata EK, That apostrophe means that the cell contains text. Why do you care that it's there? In general, you can just leave it. It' won't show up in the cell, won't print. -- Earl Kiosterud www.smokeylake.com "EK" <EK@discussions.microsoft.com> wrote in message news:2E931AF1-5CFC-4D53-9847-D68532584DCE@microsoft.com... > How do I remove the hidden apostrophy in a cell (see e.g.)? I hav...

Vlookup next row
I am attempting to use vlookup with values that have many decimal places. The problem is they are close enough for an exact match so I must use an approximate. I always get a value that is one before the value I wish to have because it takes a value lesser than the one I am trying to match. Is there a way I can get it to take the value in the next row? The numbers are in numerical order. Thanks One way: Instead of =VLOOKUP(A1,J:K,2,TRUE) use =INDEX(K:K,MATCH(A1,J:J,TRUE)+1) In article <4363DF28-A994-4D9A-9C84-3E3AA7BC6800@microsoft.com>, "Mark" <Mar...

vlookup for words content when there are duplicate values
Hi, Can somebody help me here. Thanks in advance. When I vlookup Material 1 to Material 2 for the Project No, I get the vlookup Project No. as 222-56 for all the duplicate Material values. How formula to use so that when there are duplicate values in Material2, I want to have vlookup searching to match Project No. containg "AA" as 1st choice ? (if no Project No. containing "AA", then looks for its content "AB" and subsequently looks for "AC" if there is no "AB"). ColumnA ColumnB ColumnC Material 2 Duplicate? Project No. 224410 duplica...

Vlookup #8
I have the following worksheets: IAN OneTravel I have the following headers: IAN: PropertyID PropertyName Address1 City State Country ZipCode ONEIAN OneTravel: PropertyID PropertyName Address1 City State Country ZipCode ONECON These are from columns A:H. I am trying to do a lookup in IAN (colum I) that use the ONEIAN column and lookup to find a match in the ONECO column in the OneTravel worksheet and send back the PropertyID colum value from the OneTravel worksheet. I tried this: =VLOOKUP(I20864,OneTravel!A1:H31814,1,0) It didn't work. Help ----------------------------...

Excel VLOOKUP hyperlink to mailto function on different worksheet
I am trying to use VLOOKUP to pull email addresses from another worksheet that are a functioning link to outlook. The links work on the original sheet but not through the VLOOKUP. Here is the formula I am using, any help would be greatly apprecieted. =HYPERLINK(VLOOKUP(A2,Processors!$A$1:$B$50,2,FALSE)). Right now when I click the link I receive the error: Cannot open specified file. ...

Simple Vlookup Loop
Hi Everyone, Can anyone help me with looping through the worksheets with simple vlookup? - I have this workbook called "A" which has 10 different tabs - Lookup file: C:\documents\ABC.xls - Lookup range: A1:B30 - Lookup value: Workbook A, Everytab, Column C (non-empty cells) i.e. Vlookup(Cx, Lookup range, 2, false) for every worksheet I got up to below so far and totally lost what I have to do after this :( Can anyone help please? Thank you for your help in advance. LookFName = "C:\Documents\ABC.xls Set Lookbk = Workbooks.Open(Filename:=LookFName)...

Vlookup Plus...?
Hi, I wish to create a roster tool for managers (who are very basic Excel users) to plan the shifts of workers. There are a selection of standard shifts but non-standard shifts are often needed. The required shifts change each pay period. Sheet 2 is called Shifts. Row 1 columns A to L is as follows Shift StartTime FinishTime MealBreak HrsPaid Mon Tue Wed Thu Fri Sat Sun Row 2 and following will have the ShiftName in column A and then the detail of the standard shifts across the row. Sheet 1 is called Roster. Row 1 columns A to L is as follows Shift StartTime FinishTime Meal...

VLOOKUP -- DYNAMIC TABLE_ARRAY VALUE
I am trying to build a dataset (table) of values compiled from the contents of many input worksheets. I would like to know if there is a way to pass the table_array argument to vlookup based upon a text value stored in a cell (which represents a named range) on another worksheet. For example, the name DW_160 contains the range of the data stored in the worksheet "BOOK 160", the name DW_185 contans the range of the data stored in the worksheet "BOOK 185". Each of these sheets contain various products and their prices. Each of these sheets is in identical format. I...

If Vlookup is error ( #NA)
I have this formula =IF(VLOOKUP(RU!C3,data!$E$3:$R$16,2,FALSE)>0,(VLOOKUP(RU!C3,data!$E$3:$R$16,2,FALSE)),"") that is producing #NA if it doesn't find C3. I would instead like the result to be blank "" when it produces an #NA Thanks, Steve Maybe this, but not completely sure. =IF(ISERROR(VLOOKUP([RU]Sheet1!C3,data!$E$3:$R$16,2,FALSE)),0,((VLOOKUP([RU]Sheet1!C3,data!$E$3:$R$16,2,FALSE)))) HTH Regards, Howard "Steve" <Steve@discussions.microsoft.com> wrote in message news:C900AB0D-849F-4BA9-8309-5D76657ECA5D@microsoft.com......

Vlookup, sumproduct, if, sumif... erggg!
Sigh.... Can only get it to work halfway. I'm thinking I've had to much caffeine and overlooking something. Or my very basic skills just are not up to the task. LOL Sometimes a little knowlege sometimes gets dangerous! What I have and need: *2 worksheets:BILLS(think checkbook register), RAP (think bank statement) *if column L on RAP matches column W on BILLS return value in column P on BILLS even if it does not match. Sometimes things are slightly off due to taxes. *I then use this number to subtract from column O on RAP to show differences froms the logged bills vs the...

vlookup problem #12
Excel 2002 SP3 Win XP HE SP1 *Follow-up group is: microsoft.public.excel* *only reply to: microsoft.public.excel* hi, i am having a problem with vlookup wherein if i use formula A: =VLOOKUP(A2,NJ!$C$2:$C$48,1, FALSE) it works but formula B doesn't: =VLOOKUP(A2,NJ!$A$2:$E$48,5,FALSE) the only difference is the table array range which in the formula B looks into the same sheet etc., but at more columns. i can't understand what is causing the problem. i believe that the columns do not need to be sorted (help file); the sheets are formatted properly, etc. i've done LOTS of vloo...

VLookUp and DropDown List in the same cell
Hi everyone, I know that this is possible but I'm not sure how to accomplish this. In column E, I have a list of zip code. In column I, I have the "Priority" category. Priority will be based on the zip code in column E. This will be accomplished by VLookUp formula, where I will have a separate sheet store all the zip codes and the priorities (ie. Priority #1, Priority #2 and so on). The challenge part is that column I "Priority" column needs to be able to use as a Drop Down List as well, in case that the VLookUp formula needs to be overridden for s...

Vlookup + 1
What I need to do is this. I have a table that i do a look up on that looks like this. Name1 |data1|data2| | |data1|data2 Name2 |data1|data2|data1|data2| | The lookup grabs the data1 that is furthest to the right (its a date). What I then need to do is do a lookup to grab the data2 (a percentage) that is to the right of the data1. Is there someway I can get the cell address returned in my first function and then just increment the culumn being addressed by one to return the data2? Hint is use INDIRECT function. This function converts text string to range name. For instan...

Vlookup and multiple data
this is in my 1st work sheet (a1) 500 (b1)Bob (c1)Yes (d1)hants (A2) 500 (b2)Dave (c2)No (d2)surrey (A3) 500 (b3)Rob (c3)Yes (d3)york this is the formula i am using in the next worksheet for a drop down list =IF(VLOOKUP(C8,PowerGate!A3:$L$100,3,FALSE)=0,"",VLOOKUP(C8,PowerGate!A3:$L$100,3,FALSE)) It will show the 500 seperately but what i would like to do is when you select the 500 is to have all of the information for 500 shown in the next sheet.I don't want to have to select it individually. Hope this makes sense. Do you want the data for all rows wit...

IF and VLOOKUP
Hello: Here's my array: A B C 1 Band Service Target 2 6 SAP 95% 3 7 SAP 96% 4 8 SAP 97% 5 9 SAP 80% 6 10 SAP 70% 7 AP SAP 45% 8 6 ORA 93% 9 7 ORA 92% 10 8 ORA 95% 11 9 ORA 90% 12 10 ORA 70% 13 AP ORA 20% 14 6 OTH 80% 15 7 OTH 85% 16 8 OTH 90% 17 9 OTH 85% 18 10 OTH 60% 19 ...

Adding text to a vlookup cell
Can a cell be edited if the contents are the result of a vlookup function and still keep the rseults? I have 2003 on an XP Pro PC. My formula is =IF(A4<>"",VLOOKUP(A4,InvDetail!$A$2:$B$3000,2,FALSE),"") I trying to create an availability list for our customers but need to add comments to the end of some items. I know I can add another column but the customers are used to the comment being after the item description and space on the page is limited. Thanks, Lee Coleman If you're talking about entering comments manually, then you'd need to concatenate th...

Vlookup Or ccount if comands
I want to to display the data in column B if column A="Sack" I assume the Vlookup will work or the count if. Many thanks Easiest is to use autofilter on col A, filter for "Sack" -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Steve" <ball.steve@gmail.com> wrote in message news:498213ae-0d27-4d5f-9414-b182e6219738@j44g2000hsj.googlegroups.com... >I want to to display the data in column B if column A="Sack" > > I assume the Vlookup will work or the count if. > > Many thanks ...