# Match and Index's

#### seenfresh

##### Well-known Member
I have in c/A A3:A109 data that contains station names

I also have in c/B B114:B233 a list of station names, some match c/A's list and some don't

I am trying to create a formula in c/A A114:A233 that will match

1] c/A A3:A109 to c/B B114:B233 and
2] replace with c/B when match is found

I have tried and came up with this it is wrong can anyone help me with this formula:

=MATCH(A3,B114:B233,0)*INDEX(A3,0,1)

Thx

Sean

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
I have not understood completely your requirements.
anyhow I have given a sample srpeadsheet and the formulas in C13 toc17.
does this give anything near to your reqirements. or does this give you some idea how to about to find a solution to your requiremens.
formula in B13 is just copied down

confirm
venkat
Book1
ABCD
1
2
3a
4s
5d
6f
7g
8h
9j
10k
11
12
13aa
14dw
15w#N/A
16e#N/A
17r#N/A
Sheet2

Not sure I understand what you're trying to accomplish. If you get a match between column A and column B, you want to replace the value in column A with the value in column B? But weren't they the same to get a match? So what would replacing the value do?

In the spreadsheet above my objective is to in cell A13 to match A3 with B13 and if successful to insert the letter a, IF ISNA to insert either #N/A or enter 0

Second I would like to Conditional Format this equation so that when ever the match is TRUE to C/F and highlight the cell A13.

This would be useful for me when I have a list of comparable data that I can just insert into column B13 and a formula in A13 with run a match and offset the value if true, then I can run a C/F to highlight the True matches

Hope this makes sense and can be of use,

Thanks a lot guys I appreciate all your help!

Sean

Perhaps you want this formula in A13 copied down

=VLOOKUP(B13,A\$3:A\$10,1,0)

perhaps that would work best! How could I Conditional Format the cells based on the value of the VLOOKUP?

If I write IF(ISNA(VLOOKUP(B13,A\$3:A\$10,1,0)),0,(VLOOKUP(B13,A\$3:A\$10,1,0)))

Then I can set the C/F to C/F if value equals 0:

1) How could I create the C/F formula because it would have to be a formula is function in order for me to copy it down the cells from \$A13:\$A10 or even a macro or else I would have to set the C/F for each cell, which I don't want to do because it would take way too long...

Thanks again

Sean

You could just use the ISNA() part of the formula in the CF. This will give you a TRUE/FALSE result which is what Conditional Formatting needs to trigger the formatting.

e.g. Formula Is: =ISNA(VLOOKUP(B13,A\$3:A\$10,1,0))

so if the Vlookup result is #NA then the formatting will be "on". If the Vlookup returns a value, no formatting will take place.

Great Thanks!

Replies
7
Views
400
Replies
3
Views
214
Replies
5
Views
243
Replies
1
Views
200
Replies
9
Views
401

1,203,620
Messages
6,056,330
Members
444,861
Latest member
B4you_Andrea

### 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.

### Which adblocker are you using?

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

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