I made a mistake.
In Sheet 2, cell:B2, key in 400 & paste the formula in A1:
=INDEX(Sheet1!B:B,MATCH(Sheet2!B1,Sheet1!A:A,0))

"vcff" wrote:

base on the e.g below, when key in the ref no into B1, I want the "color"
shown on A1. Thanks in advance.

"vcff" wrote:

there was an error when I paste the formula. Is there something I miss out?

" wrote:

Step 1: Assume there are sheets, 1 & 2.
Step 2: Sheet 1 holds the data.
Step 3: in Sheet 2, A1 past this formula.

=index(sheet1!B:B,match(sheet2!a1,Sheet1!A:A,0))


"vcff" wrote:

I am new to formulas, so very confuse regarding the above.
I only understand that using vlookup, the data to lookup must be the
leftmost col.

Base on the following e.g.,

colA colB colC ..... colH
row1 100 red
row2 200 blue
row3 300 black
row4 400 white

formula to show "black" on colA, row1 when 300 was typed into colB, row1.

Can help and show me the formula?

Thanks