Previous Topic Next Topic

Excel issue

Closed
Nihari Posts 3 Registration date Monday July 7, 2014 Status Member Last seen July 8, 2014 - Jul 7, 2014 at 05:38 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jul 10, 2014 at 11:03 AM
Hi,


Please help me with this query asap!!

I have data in 3 worksheets.

have to compare

C column of sheet 3 = MATCH ( data in A column of sheet 1 with B column of sheet 2 )

and if data matches....

retrieve all other values in sheet 1 and 2 to sheet 3


How do i use Match and retrieve functions at the same time?
Related:

2 responses

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jul 7, 2014 at 10:43 AM
Hi Nihari,

Something like:
=IF(AND(C1=Sheet1!A1,C1=Sheet2!B1),"Pick the value you want to place here","")

Let us know how that works out for you.

Best regards,
Trowa
Nihari Posts 3 Registration date Monday July 7, 2014 Status Member Last seen July 8, 2014
Jul 8, 2014 at 06:27 AM
Another query?!

In the above mentioned scenario what if the values in either sheets (Sheet 1 or 2) is jumbled?
then for matching them i have search one value in sheet 1 with all the values of sheet 2 and then find the correct match and retrieve it to Sheet 3



Sheet 1

COLS:- A B C D
(value) (value) (date) (date)


Sheet 2

COLS:- D E F
dd/mm/yy 10 20
3/2/12 11 40
1/1/12 12 30


Sheet 3

COLS:- W X
mm/dd/yy 30
4/23/13 20
3/3/13 40



now, if C1= MATCH( F1=X1/X2/X3...and so on), then? i have get the dates and values from sheets 2 & 3 into Sheet1.

in case no
How do I solve this?

Pls Help!
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jul 10, 2014 at 11:03 AM
Hi Nihari,

Let me check if I get this right.

According to your example, sheet 1 would look like: 

10	20	dd/mm/yy	4/23/13
11	40	3/2/12	        3/3/13
12	30	1/1/12	        mm/dd/yy


Well, we could use VLOOKUP function if you can change your data setup on sheet 3: Switch column W with X (So W has values and X has dates).

Now relocate data from sheet 2 to sheet 1:
Sheet 1, A1: =Sheet2!E1
Sheet 1, B1: =Sheet2!F1
Sheet 1, C1: =Sheet2!D1

Now use this formula:
Sheet 1, D1: =VLOOKUP(B1,Sheet3!W1:X3,2,0)


If, for some reason, you can't change your data on sheet 3, it's also possible to use VBA for this.

Best regards,
Trowa
Answer 2 / 2
swethacharan Posts 1 Registration date Monday July 7, 2014 Status Member Last seen July 7, 2014
Jul 7, 2014 at 11:01 AM
Thanks it worked for me.
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jul 7, 2014 at 11:58 AM
Great!
Nihari Posts 3 Registration date Monday July 7, 2014 Status Member Last seen July 8, 2014
Jul 8, 2014 at 06:07 AM
Thank u soo much TrowaD. U r a genius!

Similar discussions

Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses
How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
Unable to change date format in Excel
rznbyz -
Tobby -

39 responses