Forum Discussion
match and return a value
I am not a great formula writter 🙂 I would like to write a formula that does the following please
If sheet 1 Col. A matches sheet 2 Col. A then return the value of sheet 2 Col. H to sheet 1 Col I
Hi Hanneke_Mason ,
Apparently, you are looking for a VLOOKUP. Here is an example:
=VLOOKUP(Sheet1!A2, Sheet2!$A$2:$H$11, 8, FALSE)It receives 4 arguments:
- The value that you want to look up (each cell from column A in Sheet 1).
- The range where you want to look up (columns A to H in Sheet 2).
- The position of the column that you want to return (it will look up on the first column of the range, and it will return the value from the same row). In your case, is the column H, so it is position 8.
- Always set it as FALSE, as it will prevent to return "similar" results.
I'm also sending you a file to you test how it works in practice. If you need, it is possible to add an additional formula to avoid those "#N/A"s in column I.
2 Replies
Hi Hanneke_Mason ,
Apparently, you are looking for a VLOOKUP. Here is an example:
=VLOOKUP(Sheet1!A2, Sheet2!$A$2:$H$11, 8, FALSE)It receives 4 arguments:
- The value that you want to look up (each cell from column A in Sheet 1).
- The range where you want to look up (columns A to H in Sheet 2).
- The position of the column that you want to return (it will look up on the first column of the range, and it will return the value from the same row). In your case, is the column H, so it is position 8.
- Always set it as FALSE, as it will prevent to return "similar" results.
I'm also sending you a file to you test how it works in practice. If you need, it is possible to add an additional formula to avoid those "#N/A"s in column I.
Thank you have made my day it still took a bit of fiddling to get it to do what I wanted but you pointed me in the right directionrzaneti