match, if, and vlookup

[Sharon]

I have a workbook that I need to match data from worksheet
B to Worksheet A and pull data and place it on the
original Worksheet B - see the example data below

Worksheet A
Name Result
Mark 52
Ralph 97
George 25

Worksheet B
Name Result
Joe <formula>
George
Ralph

on Worksheet A the name column is the far left column
but is the 5th column on Worksheet B

Thanks!
Sharon

 

[Domenic]

Hi Sharon,

Assuming that Column B in Worksheet A contains your results, put this
formula in F2 of Worksheet B and copy down:

=VLOOKUP(E2,A!$A$2:$B$10,2,0)

If you want the cell to be left blank if there's no match, try:

=IF(ISNA(VLOOKUP(E2,A!$A$2:$B$10,2,0)),"",VLOOKUP(E2,A!$A$2:$B$10,2,0))

Hope this helps!

 

[Myrna Larson]

In your result cell (F2?)

=VLOOKUP(E2,Sheet1!$A$2:$B$xxx,2,0)

Replace the reference to the 1st sheet as needed. BTW, you can enter a formula
like this by "pointing" to the range with the mouse.

Start the formula by typing =VLOOKUP(
then click on E2
then type the comma and click on the worksheet tab for the
1st sheet and drag over the range
then type the rest of the formula -- ",2,0)"

 

[Domenic]

Hi Zak,

Assuming that your data starts in Row 1, put this formula in F1 and copy
down:

=VLOOKUP(A1,$D$1:$E$9,2,0)

Then you can select Column F > Copy > Paste Special > Values and delete
Columns D and E.

Hope this helps!

 

[Guest]

Hi Zak,

Assuming that your data starts in Row 1, put this formula in F1 and copy
down:

=VLOOKUP(A1,$D$1:$E$9,2,0)

Then you can select Column F > Copy > Paste Special > Values and delete
Columns D and E.

Hope this helps!

 

[Guest]

Thank you so much for your help. I've figured out the formula now thanks to you. Is there any way you know for the formula to align multiple rows by the looup_value. So instead of having one col_index_nuber you could have multiple columns transfer into a new columns that are aligned? I will juse this to align one column at a time and then paste special into a new worksheet, but it would be easier if you could to it all at once. Also, what do you use the range_lookup value for? Thanks again for all your help!

 

[Guest]

Thank you so much for your help. I've figured out the formula now thanks to you. Is there any way you know for the formula to align multiple rows by the looup_value. So instead of having one col_index_nuber you could have multiple columns transfer into a new columns that are aligned? I will juse this to align one column at a time and then paste special into a new worksheet, but it would be easier if you could to it all at once. Also, what do you use the range_lookup value for? Thanks again for all your help! You know your shit!

 

[Domenic]

Hi Zak,

Now this one's a pretty one. It's basically the same sort of thing, but
with a slight difference. Here we go...

As an example, we'll use your original table, which started from Column
A to Column E. Let's say we add two more columns, Columns F and G, with
whatever values you want. Now we have Columns E, F and G we want to, as
you say, align. Are you with me so far?

Okay, so now do the following:

1) Select cells H1, I1, and J1. These cells should now be highlighted.
2) Press =
3) Enter the following array formula:
=VLOOKUP(A1,$D$1:$G$9,{2,3,4},0)
entered using CTRL+SHIFT+ENTER
4) Making sure that cells H1, I1, and J1 are all highlighted, grab the
bottom corner of cell J1 and drag down to copy the formula
5) Select Columns H, I and J, and Copy > Paste Special > Values, and
delete Columns D, E, F and G.

Hope this helps!