Need help on search and sort.

kishorkhanal

Active Member
Joined
Mar 23, 2006
Messages
434
I have text and numbers in random order in columns A to D. In column E I have the same values as in A but in a different order. Now, I want the rest of the values from B to D in columns F to H but the order should be as in E. For example value in A5 is in E10. Now I want B5, C5, D5 values in F10, G10 and H10 respectively. Is there any formula for this purpose. I tried Lookup, vlookup, match etc but didn't work.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
vlookup...

F10 formula
=Vlookup(E10,A:D,2,False)

G10
=Vlookup(E10,A:D,3,False)

H10
=Vlookup(E10,A:D,4,False)
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
In E1 put:
Code:
=VLOOKUP($E1,$A$1:$D$10,COLUMN(B1),FALSE)
Then copy E1 and paste it into all the other cells in columns E, F & G.
 

kishorkhanal

Active Member
Joined
Mar 23, 2006
Messages
434
I am still having some problems. Will this formula work if I have empty cells in columns A and E.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Yes, blanks should not be an issue.

If it's not working, it's likely that you have "Numbers Stored as Text) in column A. To resolve this, type a 1 anywhere on your sheet, copy it. Then highlight column A, paste special - values - multiply.

You might also want to check column A for EXACT match to What is in E10. Look for trailing/leading spaces.

Edit...do those steps to BOTH columns A and E.
 

Forum statistics

Threads
1,181,730
Messages
5,931,716
Members
436,800
Latest member
abowalid98

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
Top