Excel - mapping data from one range to another and getting the value from the cell to the right of the matched data
I am not that good with Excel formulas and I am trying to figure out how to first check if a cell value exists in a column and if so then get the next cell value.
Specifically, I have a range of cells, B31 to B39. What I want to do is see if those values are in cells F3-F12, and if they do, put the value from the cell directly to the right, that is, column G3-G12, at location D31-D39.
I have tried IFs, match, VLookup statements, and also tried solutions I found on the net, but it keeps returning with the wrong formula. I also tried the answer on Stack Overflow:
Excel: check if cell value exists in column and then get next cell value
but I don't really understand the formula, so I am getting lost trying to convert the ranges to a formula.
Here's the formula I'm trying to use.
= If (ISerror (Match (B31, F: F, 0), "no match", Vlookup (b31, F, G, 2, false)
Please help someone.
source to share
Place this formula in cell d31 and copy it to d39
=iferror(vlookup(b31,$f$3:$g$12,2,0),"")
Here's what's going on. VLOOKUP:
- Takes in the value (here the contents of b31),
- Looks for it in the first column of the range (f3: f12 in the range f3: g12) and
- Returns the value for the corresponding row in a column of this range (in this case, the second column or g3: g12 of the range f3: g12).
As you know, the last argument to VLOOKUP sets the type of match, with FALSE or 0 indicating an exact match.
Finally, IFERROR handles # N / A when VLOOKUP doesn't find a match.
source to share
This is how I used the formula from chuffs solution:
In Sheet1, column C5, I have first names from the same list and survey responses, but no email address. In sheet 2, in columns A1 and C1, I have the first names and email addresses, but no survey responses. I need email addresses and survey responses for my project.
With this formula, I was able to get the solution as follows by putting the mapped email addresses in column A1 of sheet 1.
=IFERROR(VLOOKUP(C5,Sheet1!$A$2:$C$654,3,0),"")
source to share
Thanks guys. You are wonderful.
I used Chuff's answer and modified it a bit to do what I wanted.
I have 2 sheets in one book.
On the first sheet, I have a list of SMS in 3 columns: phone number, date & time, message
Then I inserted a new blank column next to the phone number
On sheet 2, I have two columns: phone number, name of person
Used the formula to validate the cell on the left and match against the range in sheet 2, select the name that matches the number and enter it in a blank cell in sheet 1.
Then just copy the formula all over the column until the last sms It worked beautifully.
=VLOOKUP(A3,Sheet2!$A$1:$B$31,2,0)
source to share