Find something in column A then show the value of B for that row in Excel 2010

Question

Basically my problem is that I have a string in one cell in excel  I then need to see if that string exists in another row  not one cell but the whole row  and if so then print the contents of another cell in the same row but in another column    I will give a basic example   Title      Answer Police       15 Ambulance    20 Fire         89   Now I need to scan the title column for  say   Police  and then populate the cell with the value under Answer  in this case 15    I cant just say IF A2  Police  B2    as I need the scan the whole of the Title column   I have tried using IF COUNTIF A 2 A 100  Police    which scans the contents of A2 to A100 for the string Police  and know how to make it print a constant  just put something after the    but cant work out how to make that  constant  a variable that changes depending on the found row  So if the COUNTIF found Police in cell A44 then the answer to my formula would be B44  the same as if it found Police in A62 then my formula should show B62  I hope this makes sense and that someone can help me     Note that I am using excel 2010 and need a normal formula as I can not use scripting for this document   EDIT   Here is what I have so far  note that the spreadsheet I am using is far more complex than the  simple  example I have in the question      IF ISNUMBER FIND  RuhrP  F9   LOOKUP A9 Ruhrpumpen A 5 A 100 Ruhrpumpen I 5 I 100        This is showing  RuhrP  in every answer where  RuhrP  is found in F9 and not the answer I want which should be that found in RuhrPumpen I 5 I 100 where the cell index is the same as that for the A coloum where A9 was found  Again  sorry for the complexity I cant think of any better way to word it

User · Answer

Guys Its very interesting to know that many of us face the problem of replication of lookup value while using the Vlookup/Index with Match or Hlookup.... If we have duplicate value in a cell we all know, Vlookup will pick up against the first item would be matching in loopkup array....So here is solution for you all...

e.g.

in Column A we have field called company....

Column A                             Column B            Column C

Company_Name                         Value        
Monster                              25000                              
Naukri                               30000  
WNS                                  80000  
American Express                     40000  
Bank of America                      50000  
Alcatel Lucent                       35000  
Google                               75000  
Microsoft                            60000  
Monster                              35000  
Bank of America                      15000

Now if you lookup the above dataset, you would see the duplicity is in Company Name at Row No# 10 & 11. So if you put the vlookup, the data will be picking up which comes first..But if you use the below formula, you can make your lookup value Unique and can pick any data easily without having any dispute or facing any problem

Put the formula in C2.........A2&"_"&COUNTIF(A2:$A$2,A2)..........Result will be Monster_1 for first line item and for row no 10 & 11.....Monster_2, Bank of America_2 respectively....Here you go now you have the unique value so now you can pick any data easily now..

Cheers!!! Anil Dhawan

User · Answer

Assuming   source data range is A1 B100  query cell is D1  here you will input Police or Fire   result cell is E1  Formula in E1   VLOOKUP D1  A1 B100  2  FALSE

User · Answer

I note you suggested this formula   IF ISNUMBER FIND  RuhrP  F9   LOOKUP A9 Ruhrpumpen A 5 A 100 Ruhrpumpen I 5 I 100            but LOOKUP isn t appropriate here because I assume you want an exact match  LOOKUP won t guarantee that and also data in lookup range has to be sorted   so VLOOKUP or INDEX MATCH would be better    and you can also use IFERROR to avoid the IF function  i e   IFERROR VLOOKUP A9 Ruhrpumpen A 5 Z 100 9 0       Note  VLOOKUP always looks up the lookup value  A9  in the first column of the  table array  and returns a value from the nth column of the  table array  where n is defined by col index num  in this case 9  INDEX MATCH is sometimes more flexible because you can explicitly define the lookup column and the return column  and return column can be to the left of the lookup column which can t be the case in VLOOKUP   so that would look like this    IFERROR INDEX Ruhrpumpen I 5 I 100 MATCH A9 Ruhrpumpen A 5 A 100 0        INDEX MATCH also allows you to more easily return multiple values from different columns  e g  by using   signs in front of A9 and the lookup range Ruhrpumpen A 5 A 100  i e    IFERROR INDEX Ruhrpumpen I 5 I 100 MATCH  A9 Ruhrpumpen  A 5  A 100 0        this version can be dragged across to get successive values from column I  column J  column K etc

User · Answer

I figured out such data design   Main sheet  Column A  Pump codes  numbers   Column B  formula showing a corresponding row in sheet  Ruhrpumpen    ROW Pump codes  MATCH A2 Ruhrpumpen  I 5  I 100 0    Formulae have     instead of      it should be also German notation  If not  pleace replace   Column C  formula showing data in  Ruhrpumpen  column A from a row found by formula in col B   INDIRECT  Ruhrpumpen A  amp  B2    Column D  formula showing data in  Ruhrpumpen  column B from a row found by formula in col B    INDIRECT  Ruhrpumpen B  amp  B2    Sheet  Ruhrpumpen    Column A  some data about a certain pump  Column B  some more data  Column I  pump codes  Beginning of the list includes defined name  Pump codes  used by the formula in column B of the main sheet   Spreadsheet example  http   www bumpclub ee  jyri r Excel Data from other sheet by code row xls

[excel] Find something in column A then show the value of B for that row in Excel 2010

Examples related to excel

Examples related to excel-2010