Something like extension of Vlookup

[gurpreet161]

Hi, I need some precious help with the formula on how to do this.

Basically I have 2 spreadsheets. One with a number of items with their status in front of them. Then I have another spreadsheet with lesser number of items without any status. The items on the second spreadsheet may or may not be duplicate of the items on the first spreadsheet.

I have combined both the spreadsheets and here is how it looks:

A B Status on B
2 1 Done
5 2 Pending
6 3 Done
8 4 Pending
9 5 Being Done
10 6 Pending
7 Done
8 Done
9 Done
10 Pending


A column is taken from the second spreadsheet. B and Status on B is from the first spreadsheet.

Now, I want to mark the status on the items on the second spreadsheet (which is column A). Without manually doing it, I want to come up with a formula which tells me the status on items A. This is how the result will look:

A Status on A
2 Pending
5 Being Done
6 Pending
8 Done
9 Done
10 Pending

Can somebody help me with that?

Gurpreet

 

[Aladin Akyurek]

Hi, I need some precious help with the formula on how to do this.

Basically I have 2 spreadsheets. One with a number of items with their status in front of them. Then I have another spreadsheet with lesser number of items without any status. The items on the second spreadsheet may or may not be duplicate of the items on the first spreadsheet.

I have combined both the spreadsheets and here is how it looks:

A B Status on B
2 1 Done
5 2 Pending
6 3 Done
8 4 Pending
9 5 Being Done
10 6 Pending
7 Done
8 Done
9 Done
10 Pending


A column is taken from the second spreadsheet. B and Status on B is from the first spreadsheet.

Now, I want to mark the status on the items on the second spreadsheet (which is column A). Without manually doing it, I want to come up with a formula which tells me the status on items A. This is how the result will look:

A Status on A
2 Pending
5 Being Done
6 Pending
8 Done
9 Done
10 Pending

Can somebody help me with that?

Gurpreet

If numbers are in ascending order...

In B2 on Sheet2 enter & copy down:

=IF(LOOKUP(A2,Sheet1!$B$2:$B$11)=A2,LOOKUP(A2,Sheet1!$B$2:$B$11,Sheet1!$C$2:$C$11),"Not Found")

Otherwise:

=IF(ISNUMBER(MATCH(A2,Sheet1!$B$2:$B$11,0)),INDEX(Sheet1!$C$2:$C$11,MATCH(A2,Sheet1!$B$2:$B$11,0)),"Not Found")

 

[NBVC]

It seems that all you need is this.

=Vlookup(A1,$B$1:$C$100,2,false),

If B1:C100 is another sheet, then use something like

=Vlookup(A1,Sheet2!$B$1:$C$100,2,false)

and adjust ranges to suit.