IF Formula help or v lookup

mas_lerdanch

Board Regular
Joined
Jun 25, 2007
Messages
64
EmailPyreneesBookers.xls
ABCDEFGHIJKL
6RefNamePaxResortBoardFromDaysEmail1CustomerIDContactLastNameEmailAddress2
724508Inglis3LuzSt.SaveurHB15/06/2008324508Streetemail1@web.com
824508Inglis8LuzSt.SaveurHB15/06/2008624508Paceemail@web.com
924508Inglis11PanticosaHB11/06/2008424508Jewellemail@web.com
1016043Wood2LuzSt.SaveurHB07/06/2008616043Grayemail@web.com
117080Auger2LuzSt.SaveurHB01/07/2008147080Stevensonemailx@web.com
1220731Gray2LuzSt.SaveurHB08/07/2008520731Sheldrakeemail@web.com
1319843Cook2LuzSt.SaveurHB10/08/2008619843Williamsemail@web.com
1420731Gray2PanticosaHB13/07/2008620731Lunnemail@web.com
1519843Cook2PanticosaHB04/08/2008619843Watsonemail@web.com
1625120Goldstraw2PanticosaHB02/07/20081025120Christieemail@web.com
178436Marshallsay1LuzSt.SaveurHB02/07/200878436Wattemail@web.com
184322Burn2LuzSt.SaveurHB14/06/200874322Ridleyemail@web.com
1919741Garbutt2LuzSt.SaveurSC09/08/2008719741Spaffordemail@web.com
2028655Ryrie4LuzSt.SaveurHB17/07/2008428655Matthewsemail3@web.com
2128655Ryrie4PanticosaHB21/07/2008628655Hooperemail3@web.com
2220161Collier4LuzSt.SaveurHB25/06/2008720161Klosinemail3@web.com
2316673Pope2LuzSt.SaveurHB23/08/2008716673Doyleemail3@web.com
Sheet1


Hi I am looking to make a formula in the Email1 column that looks at Column A and if the value in Column A is equal to the value in Column J i would like to display the email address in Column L.
Please help I am at a loss as to what to do.
Thanks in advance.
Mas
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Thanks Schwarzmanne
That works well if the information I require is in the same row.
=IF(A7=J7:J26856,L7,"")
What I need is if A7 is equal to J7:J26856 i need to return the L Column
EmailPyreneesBookers.xls
ABCDEFGHIJKL
6RefNamePaxResortBoardFromDaysEmail1CustomerIDContactLastNameEmailAddress2
724508Inglis3LuzSt.SaveurHB15/06/20083 1Streetemail1@web.com
824508Inglis8LuzSt.SaveurHB15/06/200862Pace
924508Inglis11PanticosaHB11/06/200843Jewell
1016043Wood2LuzSt.SaveurHB07/06/200864Gray
117080Auger2LuzSt.SaveurHB01/07/2008145Stevenson
1220731Gray2LuzSt.SaveurHB08/07/200856Sheldrake
1319843Cook2LuzSt.SaveurHB10/08/200867Williams
1420731Gray2PanticosaHB13/07/200868Lunn
Sheet1


So A7 needs to look at the whole Column J and return the value from the cells below L20180 in this case - Columns J K & L end at row 26857
EmailPyreneesBookers.xls
ABCDEFGHIJKL
2018024508Inglispeteringlis@mail.net
2018124509Robertsbonnonmatrix@mail.co.uk
Sheet1
 
Upvote 0
Hi,

Does VLOOKUP not work for this?

=VLOOKUP(A7,J:L,3,0)

Do you want an extra message within the box if the email can't be found? ie. if there is no value in column J that matched A7?
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top