CODEWITHSUNDEEP
excel
Annadate Piyush
Annadate Piyush

Reputation: 458

create a formula for excel to find a value using row and col

I have 1st table with the information:

Table with information

and this is my second table (pivot kind of...) enter image description here

i want to have the information from the 1st table into the 2nd table (2nd image). Ie. formula return me the values as checking the row as eg. Arabic and col heading as AEG and return the corresponding ar_AR ...

Upvotes: 0

Views: 41

Answers (2)

ManishChristian
ManishChristian

Reputation: 3784

Use this formula: 

=IF(VLOOKUP($A3,Sheet2!$A$2:$B$6,2,FALSE)=Sheet3!B$2,VLOOKUP(Sheet3!$A3,Sheet2!$A$2:$C$6,3,FALSE),"Value Not Found")

Assuming your data is on Sheet2 and you want the result on Sheet3. Copy paste the formula in cell B3 and drag it across all the rows and columns.

enter image description here

*Note: I've added column AEG in E just to show the sample result.

Upvotes: 1

pnuts
pnuts

Reputation: 59495

You might use a VLOOKUP to test within an IF statement whether the column is the appropriate one and then if it is another VLOOKUP to return the appropriate value, otherwise nothing: 

 =IF(VLOOKUP($G2,$A:$B,2,0)=H$1,VLOOKUP($G2,$A:$C,3,0),"")

Use whatever the appropriate cell references are for your layout.

Upvotes: 0

Related Questions