vlookup a value from a cell containing output of a formula

[kirandhawan]

In one worksheet say "sheet-1", I have column D as a derived value after concatenating values from A,B and C column. After concatenating in column D an alphanumeric value is displayed in column D like "PRIV-098876-BOOK-909898"

There is a separate sheet in the same worksheet say "sheet-2" where this derived value i.e.PRIV-098876-BOOK-909898 is entered manually in Column A and in that sheet additional corresponding information like like email address and phone number is mentioned. The order in which this data is entered in this sheet is random and not in sequence

Now I want to Vlookup column D value in "sheet-1" to populate its corresponding email address and phone number in column E and column F of "sheet-1" from "sheet-2"

vlookup is not working here. Any other automatic way to do this as I think Vlookup cannot lookup a value from a cell which has formula!

 

[AhoyNC]

There shouldn't be a problem with VLOOKUP finding a value from a cell with a formula. Since your data is not in a sequence make sure the 4th argument in the VLOOKUP is entered as 0 or FALSE.
If this doesn't work check to see if the values in Sheet 1 and Sheet 2 are equal. So in the example below

=Sheet1!D1=Sheet2!A1

Should return TRUE.

Excel Workbook
	A	B	C	D	E	F
1	PRIV-12365	BOOK	126545	PRIV-12365-BOOK-126545	EMAIL1	555-555-1234
2	PRIV-098876	BOOK	909898	PRIV-098876-BOOK-909898	EMAIL3	555-555-1236
3	PRIV-022222	BOOK	123458	PRIV-022222-BOOK-123458	EMAIL2	555-555-1235
Sheet1

Excel Workbook
	A	B	C
1	PRIV-12365-BOOK-126545	EMAIL1	555-555-1234
2	PRIV-022222-BOOK-123458	EMAIL2	555-555-1235
3	PRIV-098876-BOOK-909898	EMAIL3	555-555-1236
Sheet2

 

[kirandhawan]

Thanks for the reply! I checked the first condition and it returns value as "TRUE". In my second sheet the data is arranged in following way.

	A	B	C
555-555-1234	EMAIL1	PRIV-12365-BOOK-126545
555-555-1235	EMAIL2	PRIV-022222-BOOK-123458
555-555-1236	EMAIL3	PRIV-098876-BOOK-909898

<tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

</tbody>

Is it the case that vlookup formula works only when selected table array starts with the value to be searched?

 

[AhoyNC]

Yes, VLOOKUP can't find data to the left. You will need to use INDEX & MATCH functions.
This link and the example below should help to explain.
https://www.youtube.com/watch?v=NYN5s1fHKog

Excel Workbook
	A	B	C	D	E	F
1	PRIV-12365	BOOK	126545	PRIV-12365-BOOK-126545	EMAIL1	555-555-1234
2	PRIV-098876	BOOK	909898	PRIV-098876-BOOK-909898	EMAIL3	555-555-1236
3	PRIV-022222	BOOK	123458	PRIV-022222-BOOK-123458	EMAIL2	555-555-1235
Sheet1

Excel Workbook
	A	B	C
1	555-555-1234	EMAIL1	PRIV-12365-BOOK-126545
2	555-555-1235	EMAIL2	PRIV-022222-BOOK-123458
3	555-555-1236	EMAIL3	PRIV-098876-BOOK-909898
Sheet2

 

[kirandhawan]

Worked like a charm!! Thanks a lot!!

 

[AhoyNC]

You're welcome. Thanks for the feedback.