Finding two matching values in column A and then returning the corresponding value in column B.

[zohaib]

Hello everyone,

I have two spreadsheets. 1. Containing the data below. 2nd spreadsheet has column A listed horizontally. Lets say I want to populate those fields. For example I need SL Charges On Multiple Forms value which is 22. How to I create a formula to find SL in column A and then find Charges On Multiple Forms and return value 22?


Data
Column A Column B Column C
AHH Count Amount
Claims 1 $1
Claims Not Imported 2 $2
Charges 3 $3
Charges On Multiple Forms 4 $4
HIS 5 $5
AMM
Claims 7 $7
Claims Not Imported 8 $8
Charges 9 $9
Charges On Multiple Forms 10 $10
HIS 11 $11
ASJ
Claims 13 $13
Claims Not Imported 14 $14
Charges 15 $15
Charges On Multiple Forms 16 $16
HIS 17 $17
ASL
Claims 19 $19
Claims Not Imported 20 $20
Charges 21 $21
Charges On Multiple Forms 22 $22
HIS 23 $23
AWB
Claims 25 $25
Claims Not Imported 26 $26
Charges 27 $27
Charges On Multiple Forms 28 $28
HIS 29 $29

 

[Nebu]

Hi:

Would you care for a sample workbook.

Thanks

 

[p45cal]

See attached to get you started.

 

[zohaib]

Hello P45cal

This works the way I wanted it to thanks so much. I thought this would also work on another worksheet I have but the problem is it does not bc on that one I need the values added for all AHH, all AMM and so on. I have attachment a sample of what I am trying to do. I do not know which sum function can be added to your formula for it to sum all AHH together and all AMM together and so on in sheet2. Thanks,

Zohaib

 

[Nebu]

Hi:

Please find the attached. I had to use helper columns to achieve this , the main formula is an array formula.

Thanks

 

[zohaib]

Hello,

Nabu is there alternative way that would work without the helper column? I want to avoid having to add helper column to the data every time. Thanks for replying.

Thank you, Zohaib

 

[Somendra Misra]

Hi Zohaib,

See this file.

Regards,

 

[zohaib]

Somendra Misra,

Thanks that works