Kutools for Excel Excelユーザーのための複数の有用な数式を収集し、年齢の計算、月、年、日、週の追加、3番目の列からのvlookupと戻り値のような多くの困難な問題を迅速に解決することができます。
Kutools for Excel200便利なExcelアドイン以上で、60日に制限なく試してみることができます。 今すぐダウンロードして無料トライアル！
= VLOOKUP（D2、$ A $ 2：$ B $ 16,2、FALSE）
2。 次に、 入力します 最初の対応する値を取得するためのキーです。スクリーンショットを参照してください：
3。 セルを選択し、塗りつぶしハンドルをこの数式を適用するセルにドラッグすると、すべての対応する値が一度に返されます。 スクリーンショットを見る：
注意： 上式中、 D2 値に基づいて値を返す基準セルです。 A2：A16 比較される基準を含む列であり、 A2：B16 使用するデータ範囲。
Kutools for Excelさん リスト内の値を探します 別のデータ範囲から対応するデータを返すのにも役立ちます。
|Kutools for Excel : 300便利なExcelアドイン以上で、60日以内に自由に試してみてください.|
インストールした後 Kutools for Excel次のようにしてください：
2。 次に、をクリックします クツール > 式 > リスト内の値を探します、スクリーンショットを見る：
3。 の中に 数式ヘルパー ダイアログボックスで、次の操作を行います。
（1。）クリック ボタンの横にある テーブル・アレイ 見たいデータ範囲を選択し、 $ 相対参照を絶対参照にするには、セル参照に文字を追加します。
（2。）クリック ボタンの横にある Look_value 必要な基準値を選択します。
（3。）クリック ボタンの横にある コラム 列を選択するには、対応する値が返されます。
4. 次に、をクリックします。 Ok ボタンを押してダイアログを閉じ、最初に対応するデータが表示されている場合は、スクリーンショットを参照してください：
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 5 months agowhat if i have repeated valued in d column i.e with same name Q!,Q2,Q3,Q4 now if i use your formula i get only Q1 value i need also the 2nd,3rd,4th,matches also
To post as a guest, your comment is unpublished.· 6 months agoI want to compare 2 columns data on one sheet to a range on another sheet and return the data in the 3rd column from the 2nd sheet
To post as a guest, your comment is unpublished.· 7 months agoI have 3 columns, A B C, i'd like to get the value of A where the value in Column C matches the value in column B is that possible?
To post as a guest, your comment is unpublished.· 7 months agoHi, .
Could you explain your problem more detailed, or you can insert a screenshot here?
To post as a guest, your comment is unpublished.
To post as a guest, your comment is unpublished.· 4 months agoCan u help me plz, i'm facing the same issue?
To post as a guest, your comment is unpublished.· 1 years agoI am struggling with this and just get blanks returning.
Want to say if cell Shhet1!ED1 matches a cell in column Sheet2!C:C then provide data for adjacent cell in Sheet2!A:A
To post as a guest, your comment is unpublished.· 1 years agoPlease could you assist me, I need to do an excel formula for a petty cash spreadsheet where it looks for an account number input in Col_C which must be looked for in a list of account numbers in Col_L and if FALSE needs to return the value input in COL_F
To post as a guest, your comment is unpublished.· 1 years agoI have 3 excel columns which have values like this,
Col_A Col_B Col_C
----- ----- -----
I want the values to be copied in Column C from Column B, which are not Mache with Column A values.
I mean just copy the values from Column B, which are not available in Column A.
Is there any excel formula using which I can achieve this?
To post as a guest, your comment is unpublished.· 1 years agoHello, Hamid,
May be the following article can help you:
Please try it, thank you!
To post as a guest, your comment is unpublished.· 1 years agoHello Skyyang,
Please can you assist me with my question on help with a formula above?
Please could you assist me, I need to do an excel formula for a petty cash spreadsheet where it looks for an account number input in Col_C which must be looked for in a list of account numbers in Col_L and if FALSE needs to return the value input in COL_F
To post as a guest, your comment is unpublished.· 1 years agoI have a master list of film titles in a certain order that must stay in that order. I duplicate that list onto another spreadsheet in order to be able to manipulate it and it gets out of order because I need to group it according to what has been completed or not. On that duplicate list, I add ID numbers in the column next to the titles. After I have completed everything I need to do, I must add those ID numbers to the master list while keeping the title order on that master list. How can I match those IDs to the list without having to manually add them in the correct order?
To post as a guest, your comment is unpublished.· 1 years agoHello, Hailey,
May be the following formula may help you:
in the above formula, new is your duplicate sheet name, please replace it to your own.
Please try it, hope it can help you!
To post as a guest, your comment is unpublished.· 1 years agoHello Sir, and thanks for the formulas provided. Although I used the formula as should it gives me N/A, which as I can understand is because is not matching the criteria between Columns D and A as per your example. Now so you can have better understanding, in my workbook A2 is the criteria cell that you want to return the value based on, G1:G15359 is the column including the criteria to be compared with, A1:N15359 the data range that you want to use.
The formula is: =IF(ISNA(MATCH(Sheet2!A2,Sheet3!$G$1:$G$15359,0)),"",VLOOKUP(Sheet2!A2,Sheet3!$A$1:$N$15359,7,FALSE))
As you have noticed I am using data from two different sheets, although I don't think that this is where the real issue is, since when clicking on the NA error it indicates the Sheet2 A2 cell and the error given is: The cell currently being evaluated contains a constant. (I have checked and confirmed that the formats are set on general). Not sure if it's because the information text are emails or because in some cells there's nothing.
Will be looking forward for your response.
To post as a guest, your comment is unpublished.· 1 years agoI need to do a comparison and pull put data as below -
Sheet 1 contain Column A & B, B is blank. Sheet 2 contains Column C & D.
The entire Column C items in Sheet 2 to be compared with first row item in Column A and if any corresponding values/data are there in Column A, then Column B to be populated with data corresponding to the row item in Column D.
Column C will have a single word. Column D may or may not have data in it. Column A will have more text.
To post as a guest, your comment is unpublished.· 1 years agoI used this formula and it mostly worked but the data from the other sheet does not come on the same row to match the reference Criteria cell.
Here's my formula. can you have a look at it and see if there's anything wrong
To post as a guest, your comment is unpublished.· 1 years agoHello, Ardi,
If you want to vlookup from another worksheet, you should apply the following formula:
Note: Sheet1 is the sheet contains the original data that you want to vlookup, and you should chaneg the cell references to your need.
Please try it! Thank you!
To post as a guest, your comment is unpublished.· 2 years agoGreat brother! My first formula is working. But 2nd formula not working. I tried many times. But i can't...
To post as a guest, your comment is unpublished.· 2 years agowow, so great! good sharing thank you! it was so useful for me.
To post as a guest, your comment is unpublished.· 2 years agoYou just saved me months of stressful entries with this post. I'm so grateful!
To post as a guest, your comment is unpublished.· 2 years agoThank you some much it was very useful. Need add info, what if we have duplicate value in Col B, how to return the value for that too.
To post as a guest, your comment is unpublished.· 3 years agoGOOD KNOWLEDGE SHARING THANK YOU VERY MUCH