VLOOKUP giving wrong results

Test.xlsxHi Guys i am new to excel, I had tried many formulas but it is not working .It is always giving me the same result.for formulas which i used u can refer to the attached .please correct them or u can given me a new formula which can work out. please guys help me out .thanks in advance.
what i want is
"Taking the base reference of Crncy column in sheet1 i want every cusip in sheet 2 to under go the formulas in sheet one concatenate column for example it the curncy in sheet 1 is USD then it should undergo USD formula in sheet1 and i should get the result in sheet 2 in current price column.

I have tried some formulas but they are not at all working. please refer the attachment i one i have highlighted in sheet 2 with red are wrong one . if you compare them with cusip column in same sheet they doesn't match each other.

It's not clear what the problem is, the VLOOKUP is currently doing what it was designed to do, bring back the value from column C on sheet1 in the row that matches the CRNCY.

Please update your workbook and enter your desired results in several cells, then explain them.

This is still confusing. Tell me EXACTLY what results you are EXPECTING (work it out manually) in WHICH cells (one or two examples will do) and WHY.

Hi, Check it out the attachment with updated formulas

1. A pivot table created in NewSheet1
2. New formula updated in sheet2 Column H (it is an array formula need to be confirmed with ctrl+shift+enter

Does that help ?

The problem is with the description of your requirement. That is really confusing. In earlier attachments you were already having some formulas in Sheet1 and you were wanting to populate col. H of Sheet2. But now in your latest attached sheet you have description in Sheet1 in col. B and C also. Not sure what are you trying to achieve?

So to get proper help here, Please fill your desired output in both the sheets manually and highlight the columns in both the sheets where you want a formula to populate manually filled output along with the existing formulas which you think are working correctly. And do it considering your latest attachment. After filling the desired output, please explain how do you expect that output in a particular cell.

Is this maybe, just maybe what you want. To get SOMETHING, I have sorted both sheets in alphabetical order of currencies. I have added a helper row in sheet 2 (which you can hide).

If thi si not what you want please go back to your last sheet and tell us EXACTLY what you want in Sheet 1 Cell D2.

Wooo Hooo! A lucky guess.

So, your questions.

I had to sort it so that all the USDs in sheet 2 were beside each other. I then used your concatenate formula (from sheet 1) to make the concatenated current price column (H) in sheet 2. In column I I used a simple formula (which could also have been written as =IF(OR(D2<>D1,D1="Currency"),H2,I1&", "&H2) to add on the new price to the previous one, until the currency changed, when it started over again. Back on sheet 1 I used INDEX MATCH to return the final result.

"You haven't used the columns B and C in sheet 1 right ?" That's correct, but I did use the formula from column C (but I used it on sheet 2).



If that is it, please taker a moment to mark the thread as solved and say thanks to those who helped by clicking the Add Reputation button at the foot of their post(s).

One more questions can this formula work with out sorting the currencies .

NOT in its present form.

You're welcome!